<!DOCTYPE html>
<html lang="zh-CN">
<head>
  <meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=2">
<meta name="theme-color" content="#222">
<meta name="generator" content="Hexo 4.2.0">
  <link rel="apple-touch-icon" sizes="180x180" href="/images/apple-touch-icon-next.png">
  <link rel="icon" type="image/png" sizes="32x32" href="/images/favicon-32x32-next.png">
  <link rel="icon" type="image/png" sizes="16x16" href="/images/favicon-16x16-next.png">
  <link rel="mask-icon" href="/images/logo.svg" color="#222">

<link rel="stylesheet" href="/css/main.css">


<link rel="stylesheet" href="/lib/font-awesome/css/font-awesome.min.css">
  <link rel="stylesheet" href="//cdn.jsdelivr.net/gh/fancyapps/fancybox@3/dist/jquery.fancybox.min.css">
  <link rel="stylesheet" href="/lib/pace/pace-theme-minimal.min.css">
  <script src="/lib/pace/pace.min.js"></script>

<script id="hexo-configurations">
    var NexT = window.NexT || {};
    var CONFIG = {"hostname":"lanqilu.github.io","root":"/","scheme":"Gemini","version":"7.8.0","exturl":false,"sidebar":{"position":"left","display":"post","padding":18,"offset":12,"onmobile":true},"copycode":{"enable":true,"show_result":true,"style":null},"back2top":{"enable":true,"sidebar":true,"scrollpercent":true},"bookmark":{"enable":false,"color":"#395ca3","save":"auto"},"fancybox":true,"mediumzoom":false,"lazyload":false,"pangu":true,"comments":{"style":"tabs","active":null,"storage":true,"lazyload":false,"nav":null},"algolia":{"hits":{"per_page":10},"labels":{"input_placeholder":"Search for Posts","hits_empty":"We didn't find any results for the search: ${query}","hits_stats":"${hits} results found in ${time} ms"}},"localsearch":{"enable":true,"trigger":"auto","top_n_per_article":1,"unescape":false,"preload":false},"motion":{"enable":true,"async":false,"transition":{"post_block":"fadeIn","post_header":"slideDownIn","post_body":"slideDownIn","coll_header":"slideLeftIn","sidebar":"slideUpIn"}},"path":"search.xml"};
  </script>

  <meta name="description" content="参考书籍《SQL必知必会》第4版 Ben Forta 著">
<meta property="og:type" content="article">
<meta property="og:title" content="MySQL">
<meta property="og:url" content="https://lanqilu.github.io/2020/07/07/MySQL/index.html">
<meta property="og:site_name" content="Halo">
<meta property="og:description" content="参考书籍《SQL必知必会》第4版 Ben Forta 著">
<meta property="og:locale" content="zh_CN">
<meta property="og:image" content="http://img.whl123456.top/image/MySQL%E5%AE%98%E7%BD%91%E4%B8%8B%E8%BD%BD.png">
<meta property="og:image" content="http://img.whl123456.top/image/Navicat%E5%88%9B%E5%BB%BA%E8%A1%A8.png">
<meta property="og:image" content="http://img.whl123456.top/image/Navicat%E5%8F%AF%E8%A7%86%E5%8C%96%E6%93%8D%E4%BD%9C%E8%A1%A8.png">
<meta property="article:published_time" content="2020-07-07T01:33:36.000Z">
<meta property="article:modified_time" content="2020-08-06T08:52:56.499Z">
<meta property="article:author" content="Lanqilu">
<meta name="twitter:card" content="summary">
<meta name="twitter:image" content="http://img.whl123456.top/image/MySQL%E5%AE%98%E7%BD%91%E4%B8%8B%E8%BD%BD.png">

<link rel="canonical" href="https://lanqilu.github.io/2020/07/07/MySQL/">


<script id="page-configurations">
  // https://hexo.io/docs/variables.html
  CONFIG.page = {
    sidebar: "",
    isHome : false,
    isPost : true,
    lang   : 'zh-CN'
  };
</script>

  <title>MySQL | Halo</title>
  
    <script async src="https://www.googletagmanager.com/gtag/js?id=UA-161500949-1"></script>
    <script data-pjax>
      if (CONFIG.hostname === location.hostname) {
        window.dataLayer = window.dataLayer || [];
        function gtag(){dataLayer.push(arguments);}
        gtag('js', new Date());
        gtag('config', 'UA-161500949-1');
      }
    </script>


  <script data-pjax>
    var _hmt = _hmt || [];
    (function() {
      var hm = document.createElement("script");
      hm.src = "https://hm.baidu.com/hm.js?256151d1651e9d73ec980b2fc69de8f6";
      var s = document.getElementsByTagName("script")[0];
      s.parentNode.insertBefore(hm, s);
    })();
  </script>




  <noscript>
  <style>
  .use-motion .brand,
  .use-motion .menu-item,
  .sidebar-inner,
  .use-motion .post-block,
  .use-motion .pagination,
  .use-motion .comments,
  .use-motion .post-header,
  .use-motion .post-body,
  .use-motion .collection-header { opacity: initial; }

  .use-motion .site-title,
  .use-motion .site-subtitle {
    opacity: initial;
    top: initial;
  }

  .use-motion .logo-line-before i { left: initial; }
  .use-motion .logo-line-after i { right: initial; }
  </style>
</noscript>

</head>

<body itemscope itemtype="http://schema.org/WebPage">
  <div class="container use-motion">
    <div class="headband"></div>

    <header class="header" itemscope itemtype="http://schema.org/WPHeader">
      <div class="header-inner"><div class="site-brand-container">
  <div class="site-nav-toggle">
    <div class="toggle" aria-label="切换导航栏">
      <span class="toggle-line toggle-line-first"></span>
      <span class="toggle-line toggle-line-middle"></span>
      <span class="toggle-line toggle-line-last"></span>
    </div>
  </div>

  <div class="site-meta">

    <a href="/" class="brand" rel="start">
      <span class="logo-line-before"><i></i></span>
      <h1 class="site-title">Halo</h1>
      <span class="logo-line-after"><i></i></span>
    </a>
  </div>

  <div class="site-nav-right">
    <div class="toggle popup-trigger">
        <i class="fa fa-search fa-fw fa-lg"></i>
    </div>
  </div>
</div>




<nav class="site-nav">
  <ul id="menu" class="menu">
        <li class="menu-item menu-item-home">

    <a href="/" rel="section"><i class="fa fa-fw fa-home"></i>首页</a>

  </li>
        <li class="menu-item menu-item-tags">

    <a href="/tags/" rel="section"><i class="fa fa-fw fa-tags"></i>标签<span class="badge">39</span></a>

  </li>
        <li class="menu-item menu-item-categories">

    <a href="/categories/" rel="section"><i class="fa fa-fw fa-th"></i>分类<span class="badge">18</span></a>

  </li>
        <li class="menu-item menu-item-archives">

    <a href="/archives/" rel="section"><i class="fa fa-fw fa-archive"></i>归档<span class="badge">85</span></a>

  </li>
        <li class="menu-item menu-item-python">

    <a href="/categories/Python/" rel="section"><i class="fa fa-fw fa-code"></i>Python</a>

  </li>
        <li class="menu-item menu-item-java">

    <a href="/categories/Java/" rel="section"><i class="fa fa-fw fa-code"></i>Java</a>

  </li>
      <li class="menu-item menu-item-search">
        <a role="button" class="popup-trigger"><i class="fa fa-search fa-fw"></i>搜索
        </a>
      </li>
  </ul>
</nav>



  <div class="search-pop-overlay">
    <div class="popup search-popup">
        <div class="search-header">
  <span class="search-icon">
    <i class="fa fa-search"></i>
  </span>
  <div class="search-input-container">
    <input autocomplete="off" autocapitalize="off"
           placeholder="搜索..." spellcheck="false"
           type="search" class="search-input">
  </div>
  <span class="popup-btn-close">
    <i class="fa fa-times-circle"></i>
  </span>
</div>
<div id="search-result">
  <div id="no-result">
    <i class="fa fa-spinner fa-pulse fa-5x fa-fw"></i>
  </div>
</div>

    </div>
  </div>

</div>
    </header>

    
  <div class="reading-progress-bar"></div>


    <main class="main">
      <div class="main-inner">
        <div class="content-wrap">
          

          <div class="content post posts-expand">
            

    
  
  
  <article itemscope itemtype="http://schema.org/Article" class="post-block" lang="zh-CN">
    <link itemprop="mainEntityOfPage" href="https://lanqilu.github.io/2020/07/07/MySQL/">

    <span hidden itemprop="author" itemscope itemtype="http://schema.org/Person">
      <meta itemprop="image" content="http://img.whl123456.top/image/avatar.jpg">
      <meta itemprop="name" content="Lanqilu">
      <meta itemprop="description" content="">
    </span>

    <span hidden itemprop="publisher" itemscope itemtype="http://schema.org/Organization">
      <meta itemprop="name" content="Halo">
    </span>
      <header class="post-header">
        <h1 class="post-title" itemprop="name headline">
          MySQL
        </h1>

        <div class="post-meta">
            <span class="post-meta-item">
              <span class="post-meta-item-icon">
                <i class="fa fa-calendar-o"></i>
              </span>
              <span class="post-meta-item-text">发表于</span>

              <time title="创建时间：2020-07-07 09:33:36" itemprop="dateCreated datePublished" datetime="2020-07-07T09:33:36+08:00">2020-07-07</time>
            </span>
              <span class="post-meta-item">
                <span class="post-meta-item-icon">
                  <i class="fa fa-calendar-check-o"></i>
                </span>
                <span class="post-meta-item-text">更新于</span>
                <time title="修改时间：2020-08-06 16:52:56" itemprop="dateModified" datetime="2020-08-06T16:52:56+08:00">2020-08-06</time>
              </span>

          

        </div>
      </header>

    
    
    
    <div class="post-body" itemprop="articleBody">

      
        <div class="note info no-icon">
            <p>参考书籍《SQL必知必会》第4版 Ben Forta 著</p>
          </div>

<a id="more"></a>

<hr>
<h2 id="数据库简介"><a href="#数据库简介" class="headerlink" title="数据库简介"></a>数据库简介</h2><h3 id="数据库（DB，Data-Base）"><a href="#数据库（DB，Data-Base）" class="headerlink" title="数据库（DB，Data Base）"></a>数据库（DB，Data Base）</h3><p>保存有组织的数据的容器（通常是一个文件或一组文件）。</p>
<p>表（table） ：某种特定类型数据的结构化清单。存储在表中的数据是同一种类型的数据或清单。</p>
<p>列（column） ：表中的一个字段。所有表都是由一个或多个列组成的。</p>
<p>行（row） ：表中的一个记录。表中的数据是按行存储的，所保存的每个记录存储在自己的行内。</p>
<blockquote>
<p>如果将表想象为网格，网格中垂直的列为表列，水平行为表行。</p>
</blockquote>
<p>主键（primary key）：一列（或一组列），其值能够<strong>唯一标识</strong>表中每一行。顾客表可以使用顾客编号，而订单表可以使用订单ID。雇员表可以使用雇员ID 或雇员社会安全号。</p>
<p>表中的任何列都可以作为主键，只要它满足以下条件：</p>
<ul>
<li>任意两行都不具有相同的主键值；</li>
<li>每一行都必须具有一个主键值（主键列不允许NULL 值）；</li>
<li>主键列中的值不允许修改或更新；</li>
<li>主键值不能重用（如果某行从表中删除，它的主键不能赋给以后的新行）。</li>
</ul>
<blockquote>
<p>主键通常定义在表的一列上，但并不是必需这么做，也可以一起使用多个列作为主键。</p>
<p>在使用多列作为主键时，上述条件必须应用到所有列，所有列值的组合必须是唯一的（但单个列的值可以不唯一）。</p>
</blockquote>
<p>分为关系型数据库和非关系型数据库</p>
<ul>
<li><a href="https://baike.baidu.com/item/%E5%85%B3%E7%B3%BB%E5%9E%8B%E6%95%B0%E6%8D%AE%E5%BA%93/8999831" target="_blank" rel="noopener">关系型数据库</a>：SQL（Structured Query Language）指采用了<a href="https://baike.baidu.com/item/关系模型/3189329" target="_blank" rel="noopener">关系模型</a>来组织数据的数据库，其以行和列的形式存储数据，以便于用户理解，关系型数据库这一系列的行和列被称为表，一组表组成了<a href="https://baike.baidu.com/item/数据库/103728" target="_blank" rel="noopener">数据库</a>。</li>
<li><a href="https://baike.baidu.com/item/NoSQL/8828247?fromtitle=%E9%9D%9E%E5%85%B3%E7%B3%BB%E5%9E%8B%E6%95%B0%E6%8D%AE%E5%BA%93&fromid=6376860&fr=aladdin" target="_blank" rel="noopener">非关系型数据库</a>：NoSQL（Not Only SQL），对象存储，通过对象的属性来决定。</li>
</ul>
<h3 id="数据库管理系统（DBMS，Database-Management-System）"><a href="#数据库管理系统（DBMS，Database-Management-System）" class="headerlink" title="数据库管理系统（DBMS，Database Management System）"></a>数据库管理系统（DBMS，Database Management System）</h3><p>数据库管理软件，科学有效的管理数据。MySQL是一种DBMS，即它是一种数据库软件。</p>
<h3 id="MySQL"><a href="#MySQL" class="headerlink" title="MySQL"></a>MySQL</h3><p>优点：</p>
<ul>
<li>成本，MySQL是开放源代码的，一般可以免费使用（甚至可以免费修改）。</li>
<li>性能，MySQL执行很快（非常快）。</li>
<li>可信赖，某些非常重要和声望很高的公司、站点使用MySQL， 这些公司和站点都用MySQL来处理自己的重要数据。</li>
<li>简单，MySQL很容易安装和使用。</li>
</ul>
<p>5.7版本稳定</p>
<h2 id="MySQL的安装"><a href="#MySQL的安装" class="headerlink" title="MySQL的安装"></a>MySQL的安装</h2><h3 id="Windows下的安装"><a href="#Windows下的安装" class="headerlink" title="Windows下的安装"></a>Windows下的安装</h3><p>官网<a href="https://dev.mysql.com/downloads/mysql/" target="_blank" rel="noopener">下载地址</a>，选择下载ZIP Archive</p>
<img src="http://img.whl123456.top/image/MySQL官网下载.png" alt="MySQL官网下载" style="zoom:50%;" />

<ol>
<li><p>压缩包下载完成后解压到自己指定的文件夹中</p>
</li>
<li><p>编辑环境变量，将MySQL下的bin目录添加到Path</p>
</li>
<li><p>在<code>D:\安装路径\mysql-8.0.20</code>下新建<code>my.ini</code>配置文件</p>
</li>
<li><p>编辑<code>my.ini</code>文件</p>
<figure class="highlight shell"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br></pre></td><td class="code"><pre><span class="line">[mysql]</span><br><span class="line"><span class="meta">#</span><span class="bash"> 设置mysql客户端默认字符集</span></span><br><span class="line">default-character-set=utf8 </span><br><span class="line">[mysqld]</span><br><span class="line"><span class="meta">#</span><span class="bash">设置3306端口</span></span><br><span class="line">port = 3306 </span><br><span class="line"><span class="meta">#</span><span class="bash"> 设置mysql的安装目录</span></span><br><span class="line">basedir=D:\Develop\MySQL\mysql-8.0.20</span><br><span class="line"><span class="meta">#</span><span class="bash"> 允许最大连接数</span></span><br><span class="line">max_connections=200</span><br><span class="line"><span class="meta">#</span><span class="bash"> 服务端使用的字符集默认为8比特编码的latin1字符集</span></span><br><span class="line">character-set-server=utf8</span><br><span class="line"><span class="meta">#</span><span class="bash"> 创建新表时将使用的默认存储引擎</span></span><br><span class="line">default-storage-engine=INNODB</span><br></pre></td></tr></table></figure>

<blockquote>
<p>路径选择自己安装目录，<code>data</code>目录不要自己创建，参考<a href="https://www.jb51.net/article/172172.htm" target="_blank" rel="noopener">文章</a></p>
</blockquote>
</li>
<li><p>安装MySQL服务：在<strong>管理员模式</strong>下启动命令行终端（cmd），执行<code>mysqld -install</code>命令，安装成功则显示<code>Service successfully installed.</code></p>
</li>
<li><p>初始化数据库文件：再输入<code>mysqld --initialize-insecure --user=mysql</code>，等待一会不报错且在安装目录中生成<code>data</code>文件则表示成功</p>
</li>
<li><p>启动MySQL：键入<code>net start mysql</code>启动服务，显示<code>MySQL服务已启动成功</code></p>
</li>
<li><p>进入MySQL管理界面：在启动成功MySQL服务后，输入命令<code>mysql -u root -p</code>，Enter password：时直接回车</p>
</li>
<li><p>设置用户和密码：进入SQL命令行后输入。</p>
<figure class="highlight shell"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">update mysql.user set authentication_string=MD5('123456') where User='root' and Host='locahost';</span><br></pre></td></tr></table></figure>

<blockquote>
<p>在 MySQL5.7 中<code>user</code>表的<code>password</code>已换成了<code>authentication_string</code>。</p>
<p><code>password()</code>加密函数已经在 8.0.11 中移除了，可以使用<code>MD5()</code>函数代替。</p>
</blockquote>
</li>
<li><p>刷新权限：<code>FLUSH PRIVILEGES;</code></p>
</li>
<li><p>重启MySQL：退出MySQL命令行，输入<code>quit</code>，再在命令行中输入<code>net stop mysql</code>关闭MySQL之后启动<code>net start mysql</code></p>
</li>
</ol>
<h2 id="使用Navicat进行可视化操作"><a href="#使用Navicat进行可视化操作" class="headerlink" title="使用Navicat进行可视化操作"></a>使用Navicat进行可视化操作</h2><p>连接号Navicat后，选择新建查询，输入SQL建表语句进行创建一个school表</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">CREATE</span> <span class="keyword">DATABASE</span> <span class="string">`school`</span> <span class="built_in">CHARACTER</span> </span><br><span class="line"><span class="keyword">SET</span> utf8 <span class="keyword">COLLATE</span> utf8_general_ci;</span><br></pre></td></tr></table></figure>

<p><img src="http://img.whl123456.top/image/Navicat%E5%88%9B%E5%BB%BA%E8%A1%A8.png" alt="Navicat创建表"></p>
<p><img src="http://img.whl123456.top/image/Navicat%E5%8F%AF%E8%A7%86%E5%8C%96%E6%93%8D%E4%BD%9C%E8%A1%A8.png" alt="Navicat可视化操作表"></p>
<h2 id="SQL语句"><a href="#SQL语句" class="headerlink" title="SQL语句"></a>SQL语句</h2><ol>
<li><p>查看当前所有数据库：<code>SHOW DATABASES;</code></p>
</li>
<li><p>打开指定的库：<code>USE 库名;</code></p>
</li>
<li><p>查看当前的所有表：<code>SHOW TABLES;</code></p>
</li>
<li><p>查看其他库的所有表：<code>SHOW TABLES FROM 库名;</code></p>
</li>
<li><p>创建表</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">CREATE</span> <span class="keyword">TABLE</span> 表名(</span><br><span class="line">列名 列类型,</span><br><span class="line">列名 列类型,</span><br><span class="line">);</span><br></pre></td></tr></table></figure>
</li>
<li><p>查看表结构：<code>DESC 表名;</code></p>
</li>
<li><p>查看数据库版本：</p>
<ul>
<li>登录到MySQL<code>SELECT VERSION();</code> </li>
<li>没有登录MySQL命令行中<code>mysql -V</code></li>
</ul>
</li>
</ol>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SHOW</span> <span class="keyword">DATABASES</span>; <span class="comment">--查看所有的数据库</span></span><br><span class="line"><span class="keyword">USE</span> 数据库名 <span class="comment">--切换数据库</span></span><br><span class="line"><span class="keyword">SHOW</span> <span class="keyword">TABLES</span>; <span class="comment">--查看数据库中所有的表</span></span><br><span class="line"><span class="keyword">DESCRIBE</span> 表名; <span class="comment">--显示表中信息</span></span><br><span class="line"><span class="keyword">CREATE</span> <span class="keyword">DATABASES</span> 数据库名; <span class="comment">--创建数据库</span></span><br></pre></td></tr></table></figure>

<h3 id="语法规范"><a href="#语法规范" class="headerlink" title="语法规范"></a>语法规范</h3><p><strong>SQL</strong>（Structured Query Language）结构化查询语言的缩写。</p>
<ol>
<li>不区分大小写，建议关键字大写，表名、列名小写</li>
<li>多条SQL 语句必须以分号（;）分隔。每条命令建议用分号结尾</li>
<li>每条命令根据需要进行缩进和换行</li>
<li>注释<ul>
<li>单行注释1：#单行注释（这种形式很少得到支持）</li>
<li>单行注释2：– 单行注释</li>
<li>多行注释：/*多行注释*/</li>
</ul>
</li>
</ol>
<p>关键字（keyword）：作为SQL 组成部分的保留字。关键字不能用作表或列的名字。</p>
<h3 id="检索数据"><a href="#检索数据" class="headerlink" title="检索数据"></a>检索数据</h3><h4 id="SELECT-语句"><a href="#SELECT-语句" class="headerlink" title="SELECT 语句"></a><code>SELECT</code> 语句</h4><p>最经常使用的SQL 语句大概就是 <code>SELECT</code> 语句了。它的用途是从一个或多个表中检索信息。</p>
<h4 id="检索单个列"><a href="#检索单个列" class="headerlink" title="检索单个列"></a>检索单个列</h4><p>利用<code>SELECT</code>语句从Products 表中检索一个名为prod_name的列。所需的列名写在<code>SELECT</code>关键字之后，用<code>FROM</code> 关键字指出从哪个表中检索数据。</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> prod_name <span class="keyword">FROM</span> Products; </span><br><span class="line"><span class="comment">/* 输出结果</span></span><br><span class="line"><span class="comment">prod_name </span></span><br><span class="line"><span class="comment">-------------------</span></span><br><span class="line"><span class="comment">Fish bean bag toy</span></span><br><span class="line"><span class="comment">Bird bean bag toy</span></span><br><span class="line"><span class="comment">Rabbit bean bag toy</span></span><br><span class="line"><span class="comment">8 inch teddy bear</span></span><br><span class="line"><span class="comment">12 inch teddy bear</span></span><br><span class="line"><span class="comment">18 inch teddy bear</span></span><br><span class="line"><span class="comment">Raggedy Ann</span></span><br><span class="line"><span class="comment">King doll</span></span><br><span class="line"><span class="comment">Queen doll</span></span><br><span class="line"><span class="comment">*/</span></span><br></pre></td></tr></table></figure>

<h4 id="检索多个列"><a href="#检索多个列" class="headerlink" title="检索多个列"></a>检索多个列</h4><p>要想从一个表中检索多个列，仍然使用相同的<code>SELECT</code> 语句。唯一的不同是必须在<code>SELECT</code> 关键字后给出多个列名，列名之间必须以逗号分隔。</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> prod_id, prod_name, prod_price </span><br><span class="line"><span class="keyword">FROM</span> Products;</span><br></pre></td></tr></table></figure>

<p>与前一个例子一样，这条语句使用 <code>SELECT</code> 语句从表 Products 中选择数据。在这个例子中，指定了 3 个列名，列名之间用逗号分隔。此语句的输出如下：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br></pre></td><td class="code"><pre><span class="line">prod_id       prod_name                prod_price </span><br><span class="line">---------     --------------------     ----------</span><br><span class="line">BNBG01        Fish bean bag toy        3.4900 </span><br><span class="line">BNBG02        Bird bean bag toy        3.4900 </span><br><span class="line">BNBG03        Rabbit bean bag toy      3.4900 </span><br><span class="line">BR01          8 inch teddy bear        5.9900 </span><br><span class="line">BR02          12 inch teddy bear       8.9900 </span><br><span class="line">BR03          18 inch teddy bear       11.9900 </span><br><span class="line">RGAN01        Raggedy Ann              4.9900 </span><br><span class="line">RYL01         King doll                9.4900 </span><br><span class="line">RYL02         Queen dool               9.4900</span><br></pre></td></tr></table></figure>

<h4 id="检索所有列"><a href="#检索所有列" class="headerlink" title="检索所有列"></a>检索所有列</h4><p>除了指定所需的列外（如上所述，一个或多个列），<code>SELECT</code> 语句还可以检索所有的列而不必逐个列出它们。在实际列名的位置使用星号（<code>*</code>）通配符可以做到这点，如下所示</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">SELECT * FROM Products;</span><br></pre></td></tr></table></figure>

<blockquote>
<p>一般而言，除非你确实需要表中的每一列，否则最好别使用<code>*</code>通配符。虽然使用通配符能让你自己省事，不用明确列出所需列，但检索不需要的列通常会降低检索和应用程序的性能。</p>
<p>使用通配符有一个大优点。由于不明确指定列名（因为星号检索每一列），所以能检索出名字未知的列。</p>
</blockquote>
<h4 id="检索不同的值"><a href="#检索不同的值" class="headerlink" title="检索不同的值"></a>检索不同的值</h4><p>使用 DISTINCT 关键字，它指示数据库只返回不同的值</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> <span class="keyword">DISTINCT</span> vend_id </span><br><span class="line"><span class="keyword">FROM</span> Products;</span><br></pre></td></tr></table></figure>

<p><code>SELECT DISTINCT vend_id</code> 告诉 DBMS 只返回不同（具有唯一性）的vend_id 行，所以正如下面的输出，只有 3 行。如果使用 <code>DISTINCT</code> 关键字，它必须直接放在列名的前面。</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">vend_id </span><br><span class="line">----------</span><br><span class="line">BRS01</span><br><span class="line">DLL01</span><br><span class="line">FNG01</span><br></pre></td></tr></table></figure>

<blockquote>
<p><code>DISTINCT</code> 关键字作用于所有的列，不仅仅是跟在其后的那一列。例如，你指定 <code>SELECT DISTINCT vend_id, prod_price</code>，除非指定的两列完全相同，否则所有的行都会被检索出来</p>
</blockquote>
<h4 id="限制结果"><a href="#限制结果" class="headerlink" title="限制结果"></a>限制结果</h4><p>SELECT 语句返回指定表中所有匹配的行，很可能是每一行。如果你只想返回第一行或者一定数量的行，该怎么办呢？这是可行的，然而遗憾的是，各种数据库中的这一 SQL 实现并不相同。</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> prod_name </span><br><span class="line"><span class="keyword">FROM</span> Products;</span><br><span class="line"><span class="comment">/* 输出结果</span></span><br><span class="line"><span class="comment">prod_name </span></span><br><span class="line"><span class="comment">-------------------</span></span><br><span class="line"><span class="comment">Fish bean bag toy</span></span><br><span class="line"><span class="comment">Bird bean bag toy</span></span><br><span class="line"><span class="comment">Rabbit bean bag toy</span></span><br><span class="line"><span class="comment">8 inch teddy bear</span></span><br><span class="line"><span class="comment">12 inch teddy bear</span></span><br><span class="line"><span class="comment">18 inch teddy bear</span></span><br><span class="line"><span class="comment">Raggedy Ann</span></span><br><span class="line"><span class="comment">King doll</span></span><br><span class="line"><span class="comment">Queen doll</span></span><br><span class="line"><span class="comment">*/</span></span><br></pre></td></tr></table></figure>

<p>如果使用 MySQL、MariaDB、PostgreSQL 或者 SQLite，需要使用 <code>LIMIT</code> 子句，像这样：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> prod_name </span><br><span class="line"><span class="keyword">FROM</span> Products </span><br><span class="line"><span class="keyword">LIMIT</span> <span class="number">5</span>;</span><br><span class="line"><span class="comment">/* 输出结果</span></span><br><span class="line"><span class="comment">prod_name </span></span><br><span class="line"><span class="comment">-------------------</span></span><br><span class="line"><span class="comment">Fish bean bag toy</span></span><br><span class="line"><span class="comment">Bird bean bag toy</span></span><br><span class="line"><span class="comment">Rabbit bean bag toy</span></span><br><span class="line"><span class="comment">8 inch teddy bear</span></span><br><span class="line"><span class="comment">12 inch teddy bear</span></span><br><span class="line"><span class="comment">*/</span></span><br></pre></td></tr></table></figure>

<p>上述代码使用 SELECT 语句来检索单独的一列数据。LIMIT 5 指示 MySQL等 DBMS 返回不超过 5 行的数据。</p>
<p>为了得到后面的 5 行数据，需要指定从哪儿开始以及检索的行数</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> prod_name </span><br><span class="line"><span class="keyword">FROM</span> Products </span><br><span class="line"><span class="keyword">LIMIT</span> <span class="number">5</span> <span class="keyword">OFFSET</span> <span class="number">5</span>;</span><br><span class="line"><span class="comment">/* 输出结果</span></span><br><span class="line"><span class="comment">prod_name </span></span><br><span class="line"><span class="comment">-------------------</span></span><br><span class="line"><span class="comment">18 inch teddy bear</span></span><br><span class="line"><span class="comment">Raggedy Ann</span></span><br><span class="line"><span class="comment">King doll</span></span><br><span class="line"><span class="comment">Queen doll</span></span><br><span class="line"><span class="comment">*/</span></span><br></pre></td></tr></table></figure>

<p><code>LIMIT 5 OFFSET 5</code> 指示 MySQL 等 DBMS 返回从第 5 行起的 5 行数据。第一个数字是检索的行数，第二个数字是指从哪儿开始（从0开始计数）。</p>
<p>Products 表中只有 9 种产品，所以只返回了 4 行数据。</p>
<h3 id="排序检索数据"><a href="#排序检索数据" class="headerlink" title="排序检索数据"></a>排序检索数据</h3><p>如何使用 <code>SELECT</code> 语句的 <code>ORDER BY</code> 子句，根据需要排序检索出的数据。</p>
<h4 id="排序数据"><a href="#排序数据" class="headerlink" title="排序数据"></a>排序数据</h4><p>SQL 语句返回某个数据库表的单个列。但其输出，并没有特定的顺序。</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> prod_name </span><br><span class="line"><span class="keyword">FROM</span> Products;</span><br><span class="line"><span class="comment">/* 输出结果</span></span><br><span class="line"><span class="comment">prod_name </span></span><br><span class="line"><span class="comment">-------------------</span></span><br><span class="line"><span class="comment">Fish bean bag toy</span></span><br><span class="line"><span class="comment">Bird bean bag toy</span></span><br><span class="line"><span class="comment">Rabbit bean bag toy</span></span><br><span class="line"><span class="comment">8 inch teddy bear</span></span><br><span class="line"><span class="comment">12 inch teddy bear</span></span><br><span class="line"><span class="comment">18 inch teddy bear</span></span><br><span class="line"><span class="comment">Raggedy Ann</span></span><br><span class="line"><span class="comment">King doll</span></span><br><span class="line"><span class="comment">Queen doll</span></span><br><span class="line"><span class="comment">*/</span></span><br></pre></td></tr></table></figure>

<p>其实，检索出的数据并不是随机显示的。如果不排序，数据一般将以它在底层表中出现的顺序显示，这有可能是数据最初添加到表中的顺序。但是，如果数据随后进行过更新或删除，那么这个顺序将会受到 DBMS 重用回收存储空间的方式的影响。因此，如果不明确控制的话，则最终的结果不能（也不应该）依赖该排序顺序。关系数据库设计理论认为，如果不明确规定排序顺序，则不应该假定检索出的数据的顺序有任何意义。</p>
<blockquote>
<p>子句：SQL 语句由子句构成，有些子句是必需的，有些则是可选的。一个子句通常由一个关键字加上所提供的数据组成。子句的例子如 <code>SELECT</code> 语句的 <code>FROM</code> 子句。</p>
</blockquote>
<p>为了明确地排序用 <code>SELECT</code> 语句检索出的数据，可使用 <code>ORDER BY</code> 子句。<code>ORDER BY</code> 子句取一个或多个列的名字，据此对输出进行排序。</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> prod_name </span><br><span class="line"><span class="keyword">FROM</span> Products </span><br><span class="line"><span class="keyword">ORDER</span> <span class="keyword">BY</span> prod_name;</span><br><span class="line"><span class="comment">/* 输出结果</span></span><br><span class="line"><span class="comment">prod_name </span></span><br><span class="line"><span class="comment">-------------------</span></span><br><span class="line"><span class="comment">12 inch teddy bear</span></span><br><span class="line"><span class="comment">18 inch teddy bear</span></span><br><span class="line"><span class="comment">8 inch teddy bear</span></span><br><span class="line"><span class="comment">Bird bean bag toy</span></span><br><span class="line"><span class="comment">Fish bean bag toy</span></span><br><span class="line"><span class="comment">King doll</span></span><br><span class="line"><span class="comment">Queen doll</span></span><br><span class="line"><span class="comment">Rabbit bean bag toy</span></span><br><span class="line"><span class="comment">Raggedy Ann</span></span><br><span class="line"><span class="comment">*/</span></span><br></pre></td></tr></table></figure>

<blockquote>
<p>注意：在指定一条 <code>ORDER BY</code> 子句时，应该<strong>保证它是 <code>SELECT</code> 语句中最后一条子句</strong>。如果它不是最后的子句，将会出现错误消息</p>
</blockquote>
<h4 id="按多个列排序"><a href="#按多个列排序" class="headerlink" title="按多个列排序"></a>按多个列排序</h4><p>经常需要按不止一个列进行数据排序。例如，如果要显示雇员名单，可能希望按姓和名排序（首先按姓排序，然后在每个姓中再按名排序）。如果多个雇员有相同的姓，这样做很有用。</p>
<p>要按多个列排序，简单指定列名，列名之间用逗号分开即可（就像选择多个列时那样）。</p>
<p>下面的代码检索 3 个列，并按其中两个列对结果进行排序——首先按价格，然后按名称排序。</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> prod_id, prod_price, prod_name </span><br><span class="line"><span class="keyword">FROM</span> Products</span><br><span class="line"><span class="keyword">ORDER</span> <span class="keyword">BY</span> prod_price, prod_name;</span><br><span class="line"><span class="comment">/* 输出结果</span></span><br><span class="line"><span class="comment">prod_id     prod_price     prod_name </span></span><br><span class="line"><span class="comment">-------     ----------     -------------------- </span></span><br><span class="line"><span class="comment">BNBG02      3.4900         Bird bean bag toy </span></span><br><span class="line"><span class="comment">BNBG01      3.4900         Fish bean bag toy </span></span><br><span class="line"><span class="comment">BNBG03      3.4900         Rabbit bean bag toy </span></span><br><span class="line"><span class="comment">RGAN01      4.9900         Raggedy Ann </span></span><br><span class="line"><span class="comment">BR01        5.9900         8 inch teddy bear </span></span><br><span class="line"><span class="comment">BR02        8.9900         12 inch teddy bear </span></span><br><span class="line"><span class="comment">RYL01       9.4900         King doll </span></span><br><span class="line"><span class="comment">RYL02       9.4900         Queen doll </span></span><br><span class="line"><span class="comment">BR03        11.9900        18 inch teddy bear</span></span><br><span class="line"><span class="comment">*/</span></span><br></pre></td></tr></table></figure>

<p>上述例子中的输出，仅在多个行具有相同的 <code>prod_price</code> 值时才对产品按 <code>prod_name</code> 进行排序。如果 <code>prod_price</code> 列中所有的值都是唯一的，则不会按 <code>prod_name</code> 排序。</p>
<h4 id="按列位置排序"><a href="#按列位置排序" class="headerlink" title="按列位置排序"></a>按列位置排序</h4><p>除了能用列名指出排序顺序外，<code>ORDER BY</code> 还支持按相对列位置进行排序。</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> prod_id, prod_price, prod_name </span><br><span class="line"><span class="keyword">FROM</span> Products </span><br><span class="line"><span class="keyword">ORDER</span> <span class="keyword">BY</span> <span class="number">2</span>, <span class="number">3</span>;</span><br><span class="line"><span class="comment">/* 输出结果</span></span><br><span class="line"><span class="comment">prod_id     prod_price     prod_name </span></span><br><span class="line"><span class="comment">-------     ----------     -------------------- </span></span><br><span class="line"><span class="comment">BNBG02      3.4900         Bird bean bag toy </span></span><br><span class="line"><span class="comment">BNBG01      3.4900         Fish bean bag toy </span></span><br><span class="line"><span class="comment">BNBG03      3.4900         Rabbit bean bag toy </span></span><br><span class="line"><span class="comment">RGAN01      4.9900         Raggedy Ann </span></span><br><span class="line"><span class="comment">BR01        5.9900         8 inch teddy bear </span></span><br><span class="line"><span class="comment">BR02        8.9900         12 inch teddy bear </span></span><br><span class="line"><span class="comment">RYL01       9.4900         King doll </span></span><br><span class="line"><span class="comment">RYL02       9.4900         Queen doll </span></span><br><span class="line"><span class="comment">BR03        11.9900        18 inch teddy bear</span></span><br><span class="line"><span class="comment">*/</span></span><br></pre></td></tr></table></figure>

<p><code>ORDER BY 2</code>表示按 <code>SELECT</code> 清单中的第二个列 <code>prod_price</code> 进行排序。<code>ORDER BY 2，3</code> 表示先按 <code>prod_price</code>，再按 <code>prod_name</code> 进行排序。</p>
<p>这一技术的主要好处在于不用重新输入列名。但它也有缺点。</p>
<ul>
<li>首先，不明确给出列名有可能造成错用列名排序。</li>
<li>其次，在对 <code>SELECT</code> 清单进行更改时容易错误地对数据进行排序（忘记对 <code>ORDER BY</code> 子句做相应的改动）。</li>
<li>最后，如果进行排序的列不在 <code>SELECT</code> 清单中，显然不能使用这项技术。如果有必要，可以混合使用实际列名和相对列位置。</li>
</ul>
<h4 id="指定排序方向"><a href="#指定排序方向" class="headerlink" title="指定排序方向"></a>指定排序方向</h4><p>数据排序不限于升序排序（从 A 到 Z），这只是默认的排序顺序。还可以使用 <code>ORDER BY</code> 子句进行降序（从 Z 到 A）排序。为了进行降序排序，必须指定 <code>DESC</code> 关键字。</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> prod_id, prod_price, prod_name </span><br><span class="line"><span class="keyword">FROM</span> Products </span><br><span class="line"><span class="keyword">ORDER</span> <span class="keyword">BY</span> prod_price <span class="keyword">DESC</span>;</span><br><span class="line"><span class="comment">/* 输出结果</span></span><br><span class="line"><span class="comment">prod_id     prod_price     prod_name </span></span><br><span class="line"><span class="comment">-------     ----------     -------------------- </span></span><br><span class="line"><span class="comment">BR03        11.9900        18 inch teddy bear </span></span><br><span class="line"><span class="comment">RYL01       9.4900         King doll </span></span><br><span class="line"><span class="comment">RYL02       9.4900         Queen doll </span></span><br><span class="line"><span class="comment">BR02        8.9900         12 inch teddy bear </span></span><br><span class="line"><span class="comment">BR01        5.9900         8 inch teddy bear </span></span><br><span class="line"><span class="comment">RGAN01      4.9900         Raggedy Ann </span></span><br><span class="line"><span class="comment">BNBG01      3.4900         Fish bean bag toy </span></span><br><span class="line"><span class="comment">BNBG02      3.4900         Bird bean bag toy </span></span><br><span class="line"><span class="comment">BNBG03      3.4900         Rabbit bean bag toy</span></span><br><span class="line"><span class="comment">*/</span></span><br></pre></td></tr></table></figure>

<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> prod_id, prod_price, prod_name </span><br><span class="line"><span class="keyword">FROM</span> Products </span><br><span class="line"><span class="keyword">ORDER</span> <span class="keyword">BY</span> prod_price <span class="keyword">DESC</span>, prod_name;</span><br><span class="line"><span class="comment">/* 输出结果</span></span><br><span class="line"><span class="comment">prod_id     prod_price     prod_name </span></span><br><span class="line"><span class="comment">-------     ----------     -------------------- </span></span><br><span class="line"><span class="comment">BR03        11.9900        18 inch teddy bear </span></span><br><span class="line"><span class="comment">RYL01       9.4900         King doll </span></span><br><span class="line"><span class="comment">RYL02       9.4900         Queen doll </span></span><br><span class="line"><span class="comment">BR02        8.9900         12 inch teddy bear</span></span><br><span class="line"><span class="comment">BR01        5.9900         8 inch teddy bear </span></span><br><span class="line"><span class="comment">RGAN01      4.9900         Raggedy Ann </span></span><br><span class="line"><span class="comment">BNBG02      3.4900         Bird bean bag toy </span></span><br><span class="line"><span class="comment">BNBG01      3.4900         Fish bean bag toy </span></span><br><span class="line"><span class="comment">BNBG03      3.4900         Rabbit bean bag toy</span></span><br><span class="line"><span class="comment">*/</span></span><br></pre></td></tr></table></figure>

<p><code>DESC</code> 关键字只应用到直接位于其前面的列名。在上例中，只对 <code>prod_price</code>列指定 DESC，对 <code>prod_name</code> 列不指定。因此，<code>prod_price</code> 列以降序排序，而 <code>prod_name</code> 列（在每个价格内）仍然按标准的升序排序。</p>
<p>如果想在多个列上进行降序排序，必须对每一列指定 <code>DESC</code> 关键字。</p>
<p>请注意，<code>DESC</code> 是 <code>DESCENDING</code> 的缩写，这两个关键字都可以使用。与 <code>DESC</code>相对的是 <code>ASC</code>（或 <code>ASCENDING</code>），在升序排序时可以指定它。但实际上，<code>ASC</code> 没有多大用处，因为升序是默认的（如果既不指定 <code>ASC</code> 也不指定<code>DESC</code>，则假定为 <code>ASC</code>）。</p>
<h3 id="过滤数据"><a href="#过滤数据" class="headerlink" title="过滤数据"></a>过滤数据</h3><p>如何使用 <code>SELECT</code> 语句的 <code>WHERE</code> 子句指定搜索条件</p>
<h4 id="使用-WHERE-子句"><a href="#使用-WHERE-子句" class="headerlink" title="使用 WHERE 子句"></a>使用 <code>WHERE</code> 子句</h4><p>数据库表一般包含大量的数据，很少需要检索表中的所有行。通常只会根据特定操作或报告的需要提取表数据的子集。只检索所需数据需要指定搜索条件（search criteria），搜索条件也称为过滤条件（filter condition）。</p>
<p>在 <code>SELECT</code> 语句中，数据根据 <code>WHERE</code> 子句中指定的搜索条件进行过滤。<code>WHERE</code> 子句在表名（<code>FROM</code> 子句）之后给出</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> prod_name, prod_price </span><br><span class="line"><span class="keyword">FROM</span> Products </span><br><span class="line"><span class="keyword">WHERE</span> prod_price = <span class="number">3.49</span>;</span><br><span class="line"><span class="comment">/* 输出结果</span></span><br><span class="line"><span class="comment">prod_name               prod_price</span></span><br><span class="line"><span class="comment">-------------------     ---------- </span></span><br><span class="line"><span class="comment">Fish bean bag toy       3.49 </span></span><br><span class="line"><span class="comment">Bird bean bag toy       3.49 </span></span><br><span class="line"><span class="comment">Rabbit bean bag toy     3.49</span></span><br><span class="line"><span class="comment">*/</span></span><br></pre></td></tr></table></figure>

<p>检查这一列的值是否为指定值，据此过滤数据。不过，SQL 不止能测试等于，还能做更多的事情。</p>
<blockquote>
<p>在同时使用 <code>ORDER BY</code> 和 <code>WHERE</code> 子句时，应该让 <code>ORDER BY</code> 位于<code>WHERE</code> 之后，否则将会产生错误</p>
</blockquote>
<h4 id="WHERE-子句操作符"><a href="#WHERE-子句操作符" class="headerlink" title="WHERE 子句操作符"></a><code>WHERE</code> 子句操作符</h4><table>
<thead>
<tr>
<th>操作符</th>
<th>说明</th>
<th>操作符</th>
<th>说明</th>
</tr>
</thead>
<tbody><tr>
<td>=</td>
<td>等于</td>
<td>&gt;</td>
<td>大于</td>
</tr>
<tr>
<td>&lt;&gt;</td>
<td>不等于</td>
<td>&gt;=</td>
<td>大于等于</td>
</tr>
<tr>
<td>!=</td>
<td>不等于</td>
<td>!&gt;</td>
<td>不大于</td>
</tr>
<tr>
<td>&lt;</td>
<td>小于</td>
<td>BETEEN</td>
<td>在指定的两个值之间</td>
</tr>
<tr>
<td>&lt;=</td>
<td>小于等于</td>
<td>IS NULL</td>
<td>为NULL值</td>
</tr>
<tr>
<td>!&lt;</td>
<td>不小于</td>
<td></td>
<td></td>
</tr>
</tbody></table>
<h4 id="检查单个值"><a href="#检查单个值" class="headerlink" title="检查单个值"></a>检查单个值</h4><p>列出所有价格小于 10 美元的产品</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> prod_name, prod_price </span><br><span class="line"><span class="keyword">FROM</span> Products </span><br><span class="line"><span class="keyword">WHERE</span> prod_price &lt; <span class="number">10</span>;</span><br><span class="line"><span class="comment">/*输出结果</span></span><br><span class="line"><span class="comment">prod_name               prod_price </span></span><br><span class="line"><span class="comment">-------------------     ---------- </span></span><br><span class="line"><span class="comment">Fish bean bag toy       3.49 </span></span><br><span class="line"><span class="comment">Bird bean bag toy       3.49 </span></span><br><span class="line"><span class="comment">Rabbit bean bag toy     3.49 </span></span><br><span class="line"><span class="comment">8 inch teddy bear       5.99 </span></span><br><span class="line"><span class="comment">12 inch teddy bear      8.99 </span></span><br><span class="line"><span class="comment">Raggedy Ann             4.99 </span></span><br><span class="line"><span class="comment">King doll               9.49 </span></span><br><span class="line"><span class="comment">Queen doll              9.49</span></span><br><span class="line"><span class="comment">*/</span></span><br></pre></td></tr></table></figure>

<h4 id="不匹配检查"><a href="#不匹配检查" class="headerlink" title="不匹配检查"></a>不匹配检查</h4><p>列出所有不是供应商 DLL01 制造的产品</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> vend_id, prod_name </span><br><span class="line"><span class="keyword">FROM</span> Products </span><br><span class="line"><span class="keyword">WHERE</span> vend_id &lt;&gt; <span class="string">'DLL01'</span>;</span><br><span class="line"><span class="comment">/*输出结果</span></span><br><span class="line"><span class="comment">vend_id        prod_name </span></span><br><span class="line"><span class="comment">----------     ------------------ </span></span><br><span class="line"><span class="comment">BRS01          8 inch teddy bear</span></span><br><span class="line"><span class="comment">BRS01          12 inch teddy bear </span></span><br><span class="line"><span class="comment">BRS01          18 inch teddy bear </span></span><br><span class="line"><span class="comment">FNG01          King doll </span></span><br><span class="line"><span class="comment">FNG01          Queen doll</span></span><br><span class="line"><span class="comment">*/</span></span><br></pre></td></tr></table></figure>

<h4 id="范围值检查"><a href="#范围值检查" class="headerlink" title="范围值检查"></a>范围值检查</h4><p>要检查某个范围的值，可以使用 <code>BETWEEN</code> 操作符。其语法与其他 <code>WHERE</code>子句的操作符稍有不同，因为它需要两个值，即范围的开始值和结束值。例如，<code>BETWEEN</code> 操作符可用来检索价格在 5 美元和 10 美元之间的所有产品，或在指定的开始日期和结束日期之间的所有日期。</p>
<p>检索价格在 5 美元和 10美元之间的所有产品</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> prod_name, prod_price </span><br><span class="line"><span class="keyword">FROM</span> Products </span><br><span class="line"><span class="keyword">WHERE</span> prod_price <span class="keyword">BETWEEN</span> <span class="number">5</span> <span class="keyword">AND</span> <span class="number">10</span>;</span><br><span class="line"><span class="comment">/*输出结果</span></span><br><span class="line"><span class="comment">prod_name               prod_price </span></span><br><span class="line"><span class="comment">-------------------     ---------- </span></span><br><span class="line"><span class="comment">8 inch teddy bear       5.99 </span></span><br><span class="line"><span class="comment">12 inch teddy bear      8.99 </span></span><br><span class="line"><span class="comment">King doll               9.49 </span></span><br><span class="line"><span class="comment">Queen doll              9.49</span></span><br><span class="line"><span class="comment">*/</span></span><br></pre></td></tr></table></figure>

<p>在使用 BETWEEN 时，必须指定两个值——所需范围的低端值和高端值。这两个值必须用 AND 关键字分隔。BETWEEN 匹配范围中所有的值，<strong>包括</strong>指定的开始值和结束值。</p>
<h4 id="空值检查"><a href="#空值检查" class="headerlink" title="空值检查"></a>空值检查</h4><p>在创建表时，表设计人员可以指定其中的列能否不包含值。在一个列不包含值时，称其包含空值 NULL。</p>
<blockquote>
<p>NULL ：无值（no value），它与字段包含 0、空字符串或仅仅包含空格不同。</p>
</blockquote>
<p>确定值是否为 NULL，不能简单地检查是否= NULL。<code>SELECT</code> 语句有一个特殊的 <code>WHERE</code> 子句，可用来检查具有 NULL 值的列。这个 <code>WHERE</code> 子句就是 <code>IS NULL</code> 子句。</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> prod_name</span><br><span class="line"><span class="keyword">FROM</span> Products </span><br><span class="line"><span class="keyword">WHERE</span> prod_price <span class="keyword">IS</span> <span class="literal">NULL</span>;</span><br><span class="line"><span class="comment">/*输出结果</span></span><br><span class="line"><span class="comment">prod_name</span></span><br><span class="line"><span class="comment">-------------------</span></span><br><span class="line"><span class="comment">N/A</span></span><br><span class="line"><span class="comment">*/</span></span><br></pre></td></tr></table></figure>

<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> cust_name </span><br><span class="line"><span class="keyword">FROM</span> CUSTOMERS </span><br><span class="line"><span class="keyword">WHERE</span> cust_email <span class="keyword">IS</span> <span class="literal">NULL</span>;</span><br><span class="line"><span class="comment">/*输出结果</span></span><br><span class="line"><span class="comment">cust_name </span></span><br><span class="line"><span class="comment">---------- </span></span><br><span class="line"><span class="comment">Kids Place </span></span><br><span class="line"><span class="comment">The Toy Store</span></span><br><span class="line"><span class="comment">*/</span></span><br></pre></td></tr></table></figure>

<blockquote>
<p>通过过滤选择不包含指定值的所有行时，你可能希望返回含 NULL 值的行。但是这做不到。因为未知（unknown）有特殊的含义，数据库不知道它们是否匹配，所以在进行匹配过滤或非匹配过滤时，不会返回这些结果。</p>
<p>过滤数据时，一定要验证被过滤列中含 NULL 的行确实出现在返回的数据中。</p>
</blockquote>
<h3 id="高级数据过滤"><a href="#高级数据过滤" class="headerlink" title="高级数据过滤"></a>高级数据过滤</h3><p>如何组合 WHERE 子句以建立功能更强、更高级的搜索条件，以及学习如何使用 NOT 和 IN 操作符</p>
<h4 id="组合-WHERE-子句"><a href="#组合-WHERE-子句" class="headerlink" title="组合 WHERE 子句"></a>组合 <code>WHERE</code> 子句</h4><h5 id="AND操作符"><a href="#AND操作符" class="headerlink" title="AND操作符"></a><code>AND</code>操作符</h5><p>要通过不止一个列进行过滤，可以使用 AND 操作符给 WHERE 子句附加条件。</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> prod_id, prod_price, prod_name</span><br><span class="line"><span class="keyword">FROM</span> Products </span><br><span class="line"><span class="keyword">WHERE</span> vend_id = <span class="string">'DLL01'</span> <span class="keyword">AND</span> prod_price &lt;= <span class="number">4</span>;</span><br><span class="line"><span class="comment">/*输出结果</span></span><br><span class="line"><span class="comment">prod_id     prod_price   prod_name </span></span><br><span class="line"><span class="comment">-------     ----------   -------------------- </span></span><br><span class="line"><span class="comment">BNBG02      3.49         Bird bean bag toy </span></span><br><span class="line"><span class="comment">BNBG01      3.49         Fish bean bag toy </span></span><br><span class="line"><span class="comment">BNBG03      3.49         Rabbit bean bag toy</span></span><br><span class="line"><span class="comment">*/</span></span><br></pre></td></tr></table></figure>

<p><code>AND</code> 指示 DBMS 只返回满足所有给定条件的行。</p>
<h5 id="OR操作符"><a href="#OR操作符" class="headerlink" title="OR操作符"></a><code>OR</code>操作符</h5><p><code>OR</code> 操作符与 <code>AND</code> 操作符正好相反，它指示 DBMS 检索匹配任一条件的行。</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> prod_name, prod_price </span><br><span class="line"><span class="keyword">FROM</span> Products </span><br><span class="line"><span class="keyword">WHERE</span> vend_id = <span class="string">'DLL01'</span> <span class="keyword">OR</span> vend_id = <span class="string">'BRS01'</span>;</span><br><span class="line"><span class="comment">/*输出结果</span></span><br><span class="line"><span class="comment">prod_name               prod_price </span></span><br><span class="line"><span class="comment">-------------------     ---------- </span></span><br><span class="line"><span class="comment">8 inch teddy bear	    5.99</span></span><br><span class="line"><span class="comment">12 inch teddy bear	    8.99</span></span><br><span class="line"><span class="comment">18 inch teddy bear	    11.99</span></span><br><span class="line"><span class="comment">Fish bean bag toy	    3.49</span></span><br><span class="line"><span class="comment">Bird bean bag toy	    3.49</span></span><br><span class="line"><span class="comment">Rabbit bean bag toy  	3.49</span></span><br><span class="line"><span class="comment">Raggedy Ann	            4.99</span></span><br><span class="line"><span class="comment">*/</span></span><br></pre></td></tr></table></figure>

<h5 id="求值顺序"><a href="#求值顺序" class="headerlink" title="求值顺序"></a>求值顺序</h5><p>WHERE 子句可以包含任意数目的 <code>AND</code> 和 <code>OR</code> 操作符。允许两者结合以进行复杂、高级的过滤。</p>
<p>SQL（像多数语言一样）在处理 <code>OR</code> 操作符前，优先处理 <code>AND</code> 操作符。圆括号具有比 AND 或 OR 操作符更高的求值顺序</p>
<h4 id="IN-操作符"><a href="#IN-操作符" class="headerlink" title="IN 操作符"></a><code>IN</code> 操作符</h4><p><code>IN</code> 操作符用来指定条件范围，范围中的每个条件都可以进行匹配。<code>IN</code> 取一组由逗号分隔、括在圆括号中的合法值。</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> prod_name, prod_price </span><br><span class="line"><span class="keyword">FROM</span> Products </span><br><span class="line"><span class="keyword">WHERE</span> vend_id <span class="keyword">IN</span> ( <span class="string">'DLL01'</span>, <span class="string">'BRS01'</span> ) </span><br><span class="line"><span class="keyword">ORDER</span> <span class="keyword">BY</span> prod_name;</span><br><span class="line"><span class="comment">/*输出结果</span></span><br><span class="line"><span class="comment">prod_name               prod_price </span></span><br><span class="line"><span class="comment">-------------------     ---------- </span></span><br><span class="line"><span class="comment">12 inch teddy bear      8.99</span></span><br><span class="line"><span class="comment">18 inch teddy bear      11.99</span></span><br><span class="line"><span class="comment">8 inch teddy bear       5.99</span></span><br><span class="line"><span class="comment">Bird bean bag toy       3.49</span></span><br><span class="line"><span class="comment">Fish bean bag toy       3.49</span></span><br><span class="line"><span class="comment">Rabbit bean bag toy     3.49</span></span><br><span class="line"><span class="comment">Raggedy Ann             4.99</span></span><br><span class="line"><span class="comment">*/</span></span><br></pre></td></tr></table></figure>

<p>此 <code>SELECT</code> 语句检索由供应商 DLL01 和 BRS01 制造的所有产品。<code>IN</code> 操作符后跟由逗号分隔的合法值，这些值必须括在圆括号中。</p>
<p>同样的<code>OR</code>操作符也可以实现</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> prod_name, prod_price </span><br><span class="line"><span class="keyword">FROM</span> Products </span><br><span class="line"><span class="keyword">WHERE</span> vend_id = <span class="string">'DLL01'</span> <span class="keyword">OR</span> vend_id = <span class="string">'BRS01'</span> </span><br><span class="line"><span class="keyword">ORDER</span> <span class="keyword">BY</span> prod_name;</span><br><span class="line"><span class="comment">/*输出结果</span></span><br><span class="line"><span class="comment">prod_name               prod_price </span></span><br><span class="line"><span class="comment">-------------------     ---------- </span></span><br><span class="line"><span class="comment">12 inch teddy bear      8.99</span></span><br><span class="line"><span class="comment">18 inch teddy bear      11.99</span></span><br><span class="line"><span class="comment">8 inch teddy bear       5.99</span></span><br><span class="line"><span class="comment">Bird bean bag toy       3.49</span></span><br><span class="line"><span class="comment">Fish bean bag toy       3.49</span></span><br><span class="line"><span class="comment">Rabbit bean bag toy     3.49</span></span><br><span class="line"><span class="comment">Raggedy Ann             4.99</span></span><br><span class="line"><span class="comment">*/</span></span><br></pre></td></tr></table></figure>

<p>为什么要使用 <code>IN</code> 操作符？其优点如下。</p>
<ul>
<li>在有很多合法选项时，<code>IN</code> 操作符的语法更清楚，更直观。</li>
<li>在与其他 <code>AND</code> 和 <code>OR</code> 操作符组合使用 <code>IN</code> 时，求值顺序更容易管理。</li>
<li><code>IN</code> 操作符一般比一组 <code>OR</code> 操作符执行得更快（在上面这个合法选项很少的例子中，你看不出性能差异）。</li>
<li><code>IN</code> 的最大优点是可以包含其他 <code>SELECT</code> 语句，能够更动态地建立<code>WHERE</code> 子句。</li>
</ul>
<h4 id="NOT-操作符"><a href="#NOT-操作符" class="headerlink" title="NOT 操作符"></a><code>NOT</code> 操作符</h4><p><code>WHERE</code> 子句中的 <code>NOT</code> 操作符有且只有一个功能，那就是否定其后所跟的任何条件。</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> prod_name </span><br><span class="line"><span class="keyword">FROM</span> Products </span><br><span class="line"><span class="keyword">WHERE</span> <span class="keyword">NOT</span> vend_id = <span class="string">'DLL01'</span> </span><br><span class="line"><span class="keyword">ORDER</span> <span class="keyword">BY</span> prod_name;</span><br><span class="line"><span class="comment">/*输出结果</span></span><br><span class="line"><span class="comment">prod_name </span></span><br><span class="line"><span class="comment">------------------ </span></span><br><span class="line"><span class="comment">12 inch teddy bear </span></span><br><span class="line"><span class="comment">18 inch teddy bear </span></span><br><span class="line"><span class="comment">8 inch teddy bear </span></span><br><span class="line"><span class="comment">King doll </span></span><br><span class="line"><span class="comment">Queen doll</span></span><br><span class="line"><span class="comment">*/</span></span><br></pre></td></tr></table></figure>

<p><code>NOT</code> 否定跟在其后的条件匹配非 DLL01 之外的所有东西</p>
<blockquote>
<p>同样也可以使用<code>&lt;&gt;</code>操作符来完成</p>
<p>对于简单的 <code>WHERE</code> 子句，使用 <code>NOT</code> 确实没有什么优势。但在更复杂的子句中，<code>NOT</code> 是非常有用的。例如，在与 <code>IN</code> 操作符联合使用时，<code>NOT</code> 可以非常简单地找出与条件列表不匹配的行。</p>
</blockquote>
<h3 id="用通配符进行过滤"><a href="#用通配符进行过滤" class="headerlink" title="用通配符进行过滤"></a>用通配符进行过滤</h3><h4 id="LIKE-操作符"><a href="#LIKE-操作符" class="headerlink" title="LIKE 操作符"></a><code>LIKE</code> 操作符</h4><p>通配符：用来匹配值的一部分的特殊字符。</p>
<p>搜索模式：由字面值、通配符或两者组合构成的搜索条件。</p>
<p>通配符本身实际上是 SQL 的 <code>WHERE</code> 子句中有特殊含义的字符，SQL 支持几种通配符。为在搜索子句中使用通配符，必须使用 LIKE 操作符。<code>LIKE</code>指示 DBMS，后跟的搜索模式利用通配符匹配而不是简单的相等匹配进行比较。</p>
<p>通配符搜索只能用于文本字段（字符串），非文本数据类型字段不能使用通配符搜索。</p>
<h5 id="百分号（-）通配符"><a href="#百分号（-）通配符" class="headerlink" title="百分号（%）通配符"></a>百分号（<code>%</code>）通配符</h5><p>最常使用的通配符是百分号（<code>%</code>）。在搜索串中，<code>%</code>表示任何字符出现任意次数。</p>
<p>找出所有以词 Fish 起头的产品</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> prod_id, prod_name  </span><br><span class="line"><span class="keyword">FROM</span> Products  </span><br><span class="line"><span class="keyword">WHERE</span> prod_name <span class="keyword">LIKE</span> <span class="string">'Fish%'</span>;</span><br><span class="line"><span class="comment">/*输出结果</span></span><br><span class="line"><span class="comment">prod_id     prod_name  </span></span><br><span class="line"><span class="comment">-------     ------------------    </span></span><br><span class="line"><span class="comment">BNBG01      Fish bean bag toy</span></span><br><span class="line"><span class="comment">*/</span></span><br></pre></td></tr></table></figure>

<p><code>%</code>告诉 DBMS 接受 Fish 之后的任意字符，不管它有多少字符</p>
<blockquote>
<p>区分大小写：根据 DBMS 的不同及其配置，搜索可以是区分大小写的。如果区分大小写，则’fish%’与 Fish bean bag toy 就不匹配。MySQL默认不区分大小写</p>
</blockquote>
<p>通配符可在搜索模式中的任意位置使用，并且可以使用多个通配符。</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> prod_id, prod_name  </span><br><span class="line"><span class="keyword">FROM</span> Products  </span><br><span class="line"><span class="keyword">WHERE</span> prod_name <span class="keyword">LIKE</span> <span class="string">'%bean bag%'</span>;</span><br><span class="line"><span class="comment">/*输出结果</span></span><br><span class="line"><span class="comment">prod_id      prod_name </span></span><br><span class="line"><span class="comment">--------     --------------------  </span></span><br><span class="line"><span class="comment">BNBG01       Fish bean bag toy  </span></span><br><span class="line"><span class="comment">BNBG02       Bird bean bag toy  </span></span><br><span class="line"><span class="comment">BNBG03       Rabbit bean bag toy</span></span><br><span class="line"><span class="comment">*/</span></span><br></pre></td></tr></table></figure>

<p>搜索模式<code>%bean bag%</code>表示匹配任何位置上包含文本 bean bag 的值，不论它之前或之后出现什么字符。</p>
<p>通配符也可以出现在搜索模式字符之间。</p>
<blockquote>
<p>需要特别注意，除了能匹配一个或多个字符外，<code>%</code>还能匹配 0 个字符。<code>%</code>代表搜索模式中给定位置的 0 个、1 个或多个字符。</p>
</blockquote>
<blockquote>
<p>通配符<code>%</code>看起来像是可以匹配任何东西，但有个例外，这就是 <code>NULL</code>。</p>
</blockquote>
<h5 id="下划线（-）通配符"><a href="#下划线（-）通配符" class="headerlink" title="下划线（_）通配符"></a>下划线（<code>_</code>）通配符</h5><p>另一个有用的通配符是下划线（<code>_</code>）。下划线的用途与<code>%</code>一样，但它只匹配单个字符，而不是多个字符。</p>
<h4 id="使用通配符的技巧"><a href="#使用通配符的技巧" class="headerlink" title="使用通配符的技巧"></a>使用通配符的技巧</h4><p>通配符搜索的处理一般要比前面讨论的其他搜索所花时间更长。</p>
<ul>
<li>不要过度使用通配符。如果其他操作符能达到相同的目的，应该使用其他操作符。</li>
<li>在确实需要使用通配符时，也尽量不要把它们用在搜索模式的开始处。把通配符置于开始处，搜索起来是最慢的。</li>
<li>仔细注意通配符的位置。如果放错地方，可能不会返回想要的数据。</li>
</ul>
<h3 id="用正则表达式进行搜索"><a href="#用正则表达式进行搜索" class="headerlink" title="用正则表达式进行搜索"></a>用正则表达式进行搜索</h3><p>正则表达式是用来匹配文本的特殊的串（字符集合）</p>
<p>MySQL用<code>WHERE</code>子句对正则表达式提供了初步的支持，允许你指定正则表达式，过滤<code>SELECT</code>检索出的数据。</p>
<h4 id="基本字符匹配"><a href="#基本字符匹配" class="headerlink" title="基本字符匹配"></a>基本字符匹配</h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> prod_name</span><br><span class="line"><span class="keyword">FROM</span> products</span><br><span class="line"><span class="keyword">WHERE</span> prod_name REGEXP <span class="string">'12'</span></span><br><span class="line"><span class="keyword">ORDER</span> <span class="keyword">BY</span> prod_name;</span><br><span class="line"><span class="comment">/*输出结果</span></span><br><span class="line"><span class="comment">prod_name</span></span><br><span class="line"><span class="comment">-------=</span></span><br><span class="line"><span class="comment">12 inch teddy bear</span></span><br><span class="line"><span class="comment">*/</span></span><br></pre></td></tr></table></figure>

<p>除关键字<code>LIKE</code>被<code>REGEXP</code>替代外，这条语句看上去非常像使用<code>LIKE</code>的语句。</p>
<p>它告诉MySQL：<code>REGEXP</code>后所跟的东西作为正则表达式处理。</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> prod_name</span><br><span class="line"><span class="keyword">FROM</span> products</span><br><span class="line"><span class="keyword">WHERE</span> prod_name REGEXP <span class="string">'1. inch teddy bear'</span></span><br><span class="line"><span class="keyword">ORDER</span> <span class="keyword">BY</span> prod_name;</span><br><span class="line"><span class="comment">/*输出结果</span></span><br><span class="line"><span class="comment">prod_name</span></span><br><span class="line"><span class="comment">-------=</span></span><br><span class="line"><span class="comment">12 inch teddy bear</span></span><br><span class="line"><span class="comment">18 inch teddy bear</span></span><br><span class="line"><span class="comment">*/</span></span><br></pre></td></tr></table></figure>

<p><code>.</code>是正则表达式语言中一个特殊的字符，它表示匹配任意一个字符</p>
<h4 id="进行OR匹配"><a href="#进行OR匹配" class="headerlink" title="进行OR匹配"></a>进行OR匹配</h4><p>为搜索两个串之一（或者为这个串，或者为另一个串），使用<code>|</code></p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> prod_name</span><br><span class="line"><span class="keyword">FROM</span> products</span><br><span class="line"><span class="keyword">WHERE</span> prod_name REGEXP <span class="string">'12|18'</span></span><br><span class="line"><span class="keyword">ORDER</span> <span class="keyword">BY</span> prod_name;</span><br><span class="line"><span class="comment">/*输出结果</span></span><br><span class="line"><span class="comment">prod_name</span></span><br><span class="line"><span class="comment">-------=</span></span><br><span class="line"><span class="comment">12 inch teddy bear</span></span><br><span class="line"><span class="comment">18 inch teddy bear</span></span><br><span class="line"><span class="comment">*/</span></span><br></pre></td></tr></table></figure>

<p>使用|从功能上类似于在<code>SELECT</code>语句中使用<code>OR</code>语句，多个OR条件可并入单个正则表达式。</p>
<h4 id="匹配几个字符之一"><a href="#匹配几个字符之一" class="headerlink" title="匹配几个字符之一"></a>匹配几个字符之一</h4><p>匹配任何单一字符。但是，如果你只想匹配特定的字符，怎么办？可通过指定一组用<code>[]</code>括起来的字符来完成，如下所示：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> prod_name</span><br><span class="line"><span class="keyword">FROM</span> products</span><br><span class="line"><span class="keyword">WHERE</span> prod_name REGEXP <span class="string">'[128] inch'</span></span><br><span class="line"><span class="keyword">ORDER</span> <span class="keyword">BY</span> prod_name;</span><br><span class="line"><span class="comment">/*输出结果</span></span><br><span class="line"><span class="comment">prod_name</span></span><br><span class="line"><span class="comment">-------=</span></span><br><span class="line"><span class="comment">12 inch teddy bear</span></span><br><span class="line"><span class="comment">18 inch teddy bear</span></span><br><span class="line"><span class="comment">8 inch teddy bear</span></span><br><span class="line"><span class="comment">*/</span></span><br></pre></td></tr></table></figure>

<p><code>[128]</code>定义一组字符，的意思是匹配包含1或2或8</p>
<h4 id="匹配范围"><a href="#匹配范围" class="headerlink" title="匹配范围"></a>匹配范围</h4><p>集合可用来定义要匹配的一个或多个字符，如<code>[0-9]</code>、<code>[a-z]</code></p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> prod_name</span><br><span class="line"><span class="keyword">FROM</span> products</span><br><span class="line"><span class="keyword">WHERE</span> prod_name REGEXP <span class="string">'[1-7] inch'</span></span><br><span class="line"><span class="keyword">ORDER</span> <span class="keyword">BY</span> prod_name;</span><br><span class="line"><span class="comment">/*输出结果</span></span><br><span class="line"><span class="comment">prod_name</span></span><br><span class="line"><span class="comment">--------</span></span><br><span class="line"><span class="comment">12 inch teddy bear</span></span><br><span class="line"><span class="comment">*/</span></span><br></pre></td></tr></table></figure>

<h4 id="匹配特殊字符"><a href="#匹配特殊字符" class="headerlink" title="匹配特殊字符"></a>匹配特殊字符</h4><p>正则表达式使用中会占据一些字符，例如<code>.</code>、<code>|</code>等，如果要匹配这些字符则需要转义使用<code>\\</code></p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> vend_name</span><br><span class="line"><span class="keyword">FROM</span> vendors</span><br><span class="line"><span class="keyword">WHERE</span> vend_name REGEXP <span class="string">'\\.'</span></span><br><span class="line"><span class="keyword">ORDER</span> <span class="keyword">BY</span> vend_name;</span><br><span class="line"><span class="comment">/*输出结果</span></span><br><span class="line"><span class="comment">vend_name</span></span><br><span class="line"><span class="comment">--------</span></span><br><span class="line"><span class="comment">Doll House Inc.</span></span><br><span class="line"><span class="comment">Furball Inc.</span></span><br><span class="line"><span class="comment">*/</span></span><br></pre></td></tr></table></figure>

<p><code>\\</code>也用来引用元字符（具有特殊含义的字符）</p>
<table>
<thead>
<tr>
<th>字符</th>
<th>说明</th>
</tr>
</thead>
<tbody><tr>
<td><code>\\f</code></td>
<td>换页</td>
</tr>
<tr>
<td><code>\\n</code></td>
<td>换行</td>
</tr>
<tr>
<td><code>\\r</code></td>
<td>回车</td>
</tr>
<tr>
<td><code>\\t</code></td>
<td>制表</td>
</tr>
<tr>
<td><code>\\v</code></td>
<td>纵向制表</td>
</tr>
<tr>
<td><code>\\\</code></td>
<td>反斜杠（<code>\</code>）字符本身</td>
</tr>
</tbody></table>
<h4 id="匹配字符类"><a href="#匹配字符类" class="headerlink" title="匹配字符类"></a>匹配字符类</h4><p>存在找出你自己经常使用的数字、所有字母字符或所有数字字母字符等的匹配。为更方便工作，可以使用预定义的字符集，称为字符类</p>
<table>
<thead>
<tr>
<th>类</th>
<th>说明</th>
</tr>
</thead>
<tbody><tr>
<td><code>[:alnum:]</code></td>
<td>任意字母和数字（同<code>[a-zA-Z0-9]</code>）</td>
</tr>
<tr>
<td><code>[:alpha:]</code></td>
<td>任意字符（同<code>[a-zA-Z]</code>）</td>
</tr>
<tr>
<td><code>[:blank:]</code></td>
<td>空格和制表（同<code>[\\t]</code>）</td>
</tr>
<tr>
<td><code>[:cntrl:]</code></td>
<td>ASCII控制字符（ASCII 0到31和127）</td>
</tr>
<tr>
<td><code>[:digit:]</code></td>
<td>任意数字（同<code>[0-9]</code>）</td>
</tr>
<tr>
<td><code>[:graph:]</code></td>
<td>与<code>[:print:]</code>相同，但不包括空格</td>
</tr>
<tr>
<td><code>[:lower:]</code></td>
<td>任意小写字母（同<code>[a-z]</code>）</td>
</tr>
<tr>
<td><code>[:print:]</code></td>
<td>任意可打印字符</td>
</tr>
<tr>
<td><code>[:punct:]</code></td>
<td>既不在<code>[:alnum:]</code>又不在<code>[:cntrl:]</code>中的任意字符</td>
</tr>
<tr>
<td><code>[:space:]</code></td>
<td>包括空格在内的任意空白字符（同<code>[\\f\\n\\r\\t\\v]</code>）</td>
</tr>
<tr>
<td><code>[:upper:]</code></td>
<td>任意大写字母（同<code>[A-Z]</code>）</td>
</tr>
<tr>
<td><code>[:xdigit:]</code></td>
<td>任意十六进制数字（同<code>[a-fA-F0-9]</code>）</td>
</tr>
</tbody></table>
<h4 id="匹配多个实例"><a href="#匹配多个实例" class="headerlink" title="匹配多个实例"></a>匹配多个实例</h4><p>你可能需要寻找所有的数，不管数中包含多少数字，或者你可能想寻找一个单词并且还能够适应一个尾随的s（如果存在），等等。</p>
<table>
<thead>
<tr>
<th>元字符</th>
<th>说明</th>
</tr>
</thead>
<tbody><tr>
<td><code>*</code></td>
<td>0个或多个匹配</td>
</tr>
<tr>
<td><code>+</code></td>
<td>1个或多个匹配（等于<code>{1,}</code>）</td>
</tr>
<tr>
<td><code>？</code></td>
<td>0个或1个匹配（等于<code>{0,1}</code>）</td>
</tr>
<tr>
<td><code>{n}</code></td>
<td>指定数目的匹配</td>
</tr>
<tr>
<td><code>{n,}</code></td>
<td>不少于指定数目的匹配</td>
</tr>
<tr>
<td><code>{n,m}</code></td>
<td>匹配数目的范围（m不超过255）</td>
</tr>
</tbody></table>
<h4 id="定位符"><a href="#定位符" class="headerlink" title="定位符"></a>定位符</h4><p>匹配特定位置的文本</p>
<table>
<thead>
<tr>
<th>元字符</th>
<th>说明</th>
</tr>
</thead>
<tbody><tr>
<td><code>^</code></td>
<td>文本的开始</td>
</tr>
<tr>
<td><code>$</code></td>
<td>文本的结尾</td>
</tr>
<tr>
<td><code>[[:&lt;:]]</code></td>
<td>词的开始</td>
</tr>
<tr>
<td><code>[[:&gt;:]]</code></td>
<td>词的结尾</td>
</tr>
</tbody></table>
<h3 id="数据库定义语言（DDL）"><a href="#数据库定义语言（DDL）" class="headerlink" title="数据库定义语言（DDL）"></a>数据库定义语言（DDL）</h3><h4 id="操作数据库"><a href="#操作数据库" class="headerlink" title="操作数据库"></a>操作数据库</h4><p>CRUD：增(Create)查(Retrieve)改(Update)删(Delete)</p>
<h5 id="查-Retrieve"><a href="#查-Retrieve" class="headerlink" title="查(Retrieve)"></a>查(Retrieve)</h5><p><code>SHOW DATABASES;</code>查询所有数据库的名称</p>
<p><code>SHOW CREATE DATABASE &lt;数据库名称&gt;;</code>查询某个数据库的字符集(查询某个数据库的创建语句)</p>
<h5 id="增-Create"><a href="#增-Create" class="headerlink" title="增(Create)"></a>增(Create)</h5><p><code>CREATE DATABASE &lt;数据库名称&gt;;</code>创建数据库</p>
<p><code>CREATE DATABASE IF NOT EXISTS &lt;数据库名称&gt;;</code>创建前先进行判断</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">CREATE DATABASE IF NOT EXISTS &lt;数据库名称&gt; CHARACTER SET &lt;字符集&gt;;-- 创建指定字符集数据库</span><br></pre></td></tr></table></figure>

<h5 id="改-Update"><a href="#改-Update" class="headerlink" title="改(Update)"></a>改(Update)</h5><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">ALTER DATABASE &lt;数据库名称&gt; CHARACTER SET utf8;</span><br></pre></td></tr></table></figure>

<h5 id="删-Delete"><a href="#删-Delete" class="headerlink" title="删(Delete)"></a>删(Delete)</h5><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">DROP DATABASE IF EXISTS &lt;数据库名称&gt;;</span><br></pre></td></tr></table></figure>

<h4 id="操作表"><a href="#操作表" class="headerlink" title="操作表"></a>操作表</h4><h3 id="数据库操作语言（DML）"><a href="#数据库操作语言（DML）" class="headerlink" title="数据库操作语言（DML）"></a>数据库操作语言（DML）</h3><h3 id="数据库查询语言（DQL）"><a href="#数据库查询语言（DQL）" class="headerlink" title="数据库查询语言（DQL）"></a>数据库查询语言（DQL）</h3><p>查询表中的数据</p>
<h4 id="基础查询"><a href="#基础查询" class="headerlink" title="基础查询"></a>基础查询</h4><p>语法：<code>SELECT 查询列表 FROM 表名;</code></p>
<blockquote>
<p>可以查询表中的字段、常量值、表达式、函数</p>
<p>查询结果是一个虚拟的表格</p>
</blockquote>
<p><strong>查询表中的单个字段</strong></p>
<p>实例<code>SELECT last_name FROM employees;</code></p>
<p><strong>查询表中的多个字段</strong></p>
<p>实例<code>SELECT last_name,salary,email FROM employees;</code></p>
<p><strong>查询表中的所有字段</strong></p>
<p><code>SELECT * FROM employees;</code></p>
<h3 id="数据库控制语言（DCL）"><a href="#数据库控制语言（DCL）" class="headerlink" title="数据库控制语言（DCL）"></a>数据库控制语言（DCL）</h3><h2 id="示例数据"><a href="#示例数据" class="headerlink" title="示例数据"></a>示例数据</h2><p>建表语句</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br><span class="line">48</span><br><span class="line">49</span><br><span class="line">50</span><br><span class="line">51</span><br><span class="line">52</span><br><span class="line">53</span><br><span class="line">54</span><br><span class="line">55</span><br><span class="line">56</span><br><span class="line">57</span><br><span class="line">58</span><br><span class="line">59</span><br><span class="line">60</span><br><span class="line">61</span><br><span class="line">62</span><br><span class="line">63</span><br><span class="line">64</span><br><span class="line">65</span><br><span class="line">66</span><br><span class="line">67</span><br><span class="line">68</span><br><span class="line">69</span><br><span class="line">70</span><br><span class="line">71</span><br><span class="line">72</span><br><span class="line">73</span><br><span class="line">74</span><br><span class="line">75</span><br><span class="line">76</span><br><span class="line">77</span><br><span class="line">78</span><br><span class="line">79</span><br><span class="line">80</span><br><span class="line">81</span><br><span class="line">82</span><br><span class="line">83</span><br><span class="line">84</span><br><span class="line">85</span><br><span class="line">86</span><br><span class="line">87</span><br><span class="line">88</span><br><span class="line">89</span><br><span class="line">90</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- -----------------------------------------</span></span><br><span class="line"><span class="comment">-- Sams Teach Yourself SQL in 10 Minutes</span></span><br><span class="line"><span class="comment">-- http://forta.com/books/0672336073/</span></span><br><span class="line"><span class="comment">-- Example table creation scripts for MySQL.</span></span><br><span class="line"><span class="comment">-- -----------------------------------------</span></span><br><span class="line"></span><br><span class="line"></span><br><span class="line"><span class="comment">-- ----------------------</span></span><br><span class="line"><span class="comment">-- Create Customers table</span></span><br><span class="line"><span class="comment">-- ----------------------</span></span><br><span class="line"><span class="keyword">CREATE</span> <span class="keyword">TABLE</span> Customers</span><br><span class="line">(</span><br><span class="line">  cust_id      <span class="built_in">char</span>(<span class="number">10</span>)  <span class="keyword">NOT</span> <span class="literal">NULL</span> ,</span><br><span class="line">  cust_name    <span class="built_in">char</span>(<span class="number">50</span>)  <span class="keyword">NOT</span> <span class="literal">NULL</span> ,</span><br><span class="line">  cust_address <span class="built_in">char</span>(<span class="number">50</span>)  <span class="literal">NULL</span> ,</span><br><span class="line">  cust_city    <span class="built_in">char</span>(<span class="number">50</span>)  <span class="literal">NULL</span> ,</span><br><span class="line">  cust_state   <span class="built_in">char</span>(<span class="number">5</span>)   <span class="literal">NULL</span> ,</span><br><span class="line">  cust_zip     <span class="built_in">char</span>(<span class="number">10</span>)  <span class="literal">NULL</span> ,</span><br><span class="line">  cust_country <span class="built_in">char</span>(<span class="number">50</span>)  <span class="literal">NULL</span> ,</span><br><span class="line">  cust_contact <span class="built_in">char</span>(<span class="number">50</span>)  <span class="literal">NULL</span> ,</span><br><span class="line">  cust_email   <span class="built_in">char</span>(<span class="number">255</span>) <span class="literal">NULL</span> </span><br><span class="line">);</span><br><span class="line"></span><br><span class="line"><span class="comment">-- -----------------------</span></span><br><span class="line"><span class="comment">-- Create OrderItems table</span></span><br><span class="line"><span class="comment">-- -----------------------</span></span><br><span class="line"><span class="keyword">CREATE</span> <span class="keyword">TABLE</span> OrderItems</span><br><span class="line">(</span><br><span class="line">  order_num  <span class="built_in">int</span>          <span class="keyword">NOT</span> <span class="literal">NULL</span> ,</span><br><span class="line">  order_item <span class="built_in">int</span>          <span class="keyword">NOT</span> <span class="literal">NULL</span> ,</span><br><span class="line">  prod_id    <span class="built_in">char</span>(<span class="number">10</span>)     <span class="keyword">NOT</span> <span class="literal">NULL</span> ,</span><br><span class="line">  quantity   <span class="built_in">int</span>          <span class="keyword">NOT</span> <span class="literal">NULL</span> ,</span><br><span class="line">  item_price <span class="built_in">decimal</span>(<span class="number">8</span>,<span class="number">2</span>) <span class="keyword">NOT</span> <span class="literal">NULL</span> </span><br><span class="line">);</span><br><span class="line"></span><br><span class="line"></span><br><span class="line"><span class="comment">-- -------------------</span></span><br><span class="line"><span class="comment">-- Create Orders table</span></span><br><span class="line"><span class="comment">-- -------------------</span></span><br><span class="line"><span class="keyword">CREATE</span> <span class="keyword">TABLE</span> Orders</span><br><span class="line">(</span><br><span class="line">  order_num  <span class="built_in">int</span>      <span class="keyword">NOT</span> <span class="literal">NULL</span> ,</span><br><span class="line">  order_date datetime <span class="keyword">NOT</span> <span class="literal">NULL</span> ,</span><br><span class="line">  cust_id    <span class="built_in">char</span>(<span class="number">10</span>) <span class="keyword">NOT</span> <span class="literal">NULL</span> </span><br><span class="line">);</span><br><span class="line"></span><br><span class="line"><span class="comment">-- ---------------------</span></span><br><span class="line"><span class="comment">-- Create Products table</span></span><br><span class="line"><span class="comment">-- ---------------------</span></span><br><span class="line"><span class="keyword">CREATE</span> <span class="keyword">TABLE</span> Products</span><br><span class="line">(</span><br><span class="line">  prod_id    <span class="built_in">char</span>(<span class="number">10</span>)      <span class="keyword">NOT</span> <span class="literal">NULL</span> ,</span><br><span class="line">  vend_id    <span class="built_in">char</span>(<span class="number">10</span>)      <span class="keyword">NOT</span> <span class="literal">NULL</span> ,</span><br><span class="line">  prod_name  <span class="built_in">char</span>(<span class="number">255</span>)     <span class="keyword">NOT</span> <span class="literal">NULL</span> ,</span><br><span class="line">  prod_price <span class="built_in">decimal</span>(<span class="number">8</span>,<span class="number">2</span>)  <span class="keyword">NOT</span> <span class="literal">NULL</span> ,</span><br><span class="line">  prod_desc  <span class="built_in">text</span>          <span class="literal">NULL</span> </span><br><span class="line">);</span><br><span class="line"></span><br><span class="line"><span class="comment">-- --------------------</span></span><br><span class="line"><span class="comment">-- Create Vendors table</span></span><br><span class="line"><span class="comment">-- --------------------</span></span><br><span class="line"><span class="keyword">CREATE</span> <span class="keyword">TABLE</span> Vendors</span><br><span class="line">(</span><br><span class="line">  vend_id      <span class="built_in">char</span>(<span class="number">10</span>) <span class="keyword">NOT</span> <span class="literal">NULL</span> ,</span><br><span class="line">  vend_name    <span class="built_in">char</span>(<span class="number">50</span>) <span class="keyword">NOT</span> <span class="literal">NULL</span> ,</span><br><span class="line">  vend_address <span class="built_in">char</span>(<span class="number">50</span>) <span class="literal">NULL</span> ,</span><br><span class="line">  vend_city    <span class="built_in">char</span>(<span class="number">50</span>) <span class="literal">NULL</span> ,</span><br><span class="line">  vend_state   <span class="built_in">char</span>(<span class="number">5</span>)  <span class="literal">NULL</span> ,</span><br><span class="line">  vend_zip     <span class="built_in">char</span>(<span class="number">10</span>) <span class="literal">NULL</span> ,</span><br><span class="line">  vend_country <span class="built_in">char</span>(<span class="number">50</span>) <span class="literal">NULL</span> </span><br><span class="line">);</span><br><span class="line"></span><br><span class="line"></span><br><span class="line"><span class="comment">-- -------------------</span></span><br><span class="line"><span class="comment">-- Define primary keys</span></span><br><span class="line"><span class="comment">-- -------------------</span></span><br><span class="line"><span class="keyword">ALTER</span> <span class="keyword">TABLE</span> Customers <span class="keyword">ADD</span> PRIMARY <span class="keyword">KEY</span> (cust_id);</span><br><span class="line"><span class="keyword">ALTER</span> <span class="keyword">TABLE</span> OrderItems <span class="keyword">ADD</span> PRIMARY <span class="keyword">KEY</span> (order_num, order_item);</span><br><span class="line"><span class="keyword">ALTER</span> <span class="keyword">TABLE</span> Orders <span class="keyword">ADD</span> PRIMARY <span class="keyword">KEY</span> (order_num);</span><br><span class="line"><span class="keyword">ALTER</span> <span class="keyword">TABLE</span> Products <span class="keyword">ADD</span> PRIMARY <span class="keyword">KEY</span> (prod_id);</span><br><span class="line"><span class="keyword">ALTER</span> <span class="keyword">TABLE</span> Vendors <span class="keyword">ADD</span> PRIMARY <span class="keyword">KEY</span> (vend_id);</span><br><span class="line"></span><br><span class="line"></span><br><span class="line"><span class="comment">-- -------------------</span></span><br><span class="line"><span class="comment">-- Define foreign keys</span></span><br><span class="line"><span class="comment">-- -------------------</span></span><br><span class="line"><span class="keyword">ALTER</span> <span class="keyword">TABLE</span> OrderItems <span class="keyword">ADD</span> <span class="keyword">CONSTRAINT</span> FK_OrderItems_Orders <span class="keyword">FOREIGN</span> <span class="keyword">KEY</span> (order_num) <span class="keyword">REFERENCES</span> Orders (order_num);</span><br><span class="line"><span class="keyword">ALTER</span> <span class="keyword">TABLE</span> OrderItems <span class="keyword">ADD</span> <span class="keyword">CONSTRAINT</span> FK_OrderItems_Products <span class="keyword">FOREIGN</span> <span class="keyword">KEY</span> (prod_id) <span class="keyword">REFERENCES</span> Products (prod_id);</span><br><span class="line"><span class="keyword">ALTER</span> <span class="keyword">TABLE</span> Orders <span class="keyword">ADD</span> <span class="keyword">CONSTRAINT</span> FK_Orders_Customers <span class="keyword">FOREIGN</span> <span class="keyword">KEY</span> (cust_id) <span class="keyword">REFERENCES</span> Customers (cust_id);</span><br><span class="line"><span class="keyword">ALTER</span> <span class="keyword">TABLE</span> Products <span class="keyword">ADD</span> <span class="keyword">CONSTRAINT</span> FK_Products_Vendors <span class="keyword">FOREIGN</span> <span class="keyword">KEY</span> (vend_id) <span class="keyword">REFERENCES</span> Vendors (vend_id);</span><br></pre></td></tr></table></figure>

<p>具体数据</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br><span class="line">48</span><br><span class="line">49</span><br><span class="line">50</span><br><span class="line">51</span><br><span class="line">52</span><br><span class="line">53</span><br><span class="line">54</span><br><span class="line">55</span><br><span class="line">56</span><br><span class="line">57</span><br><span class="line">58</span><br><span class="line">59</span><br><span class="line">60</span><br><span class="line">61</span><br><span class="line">62</span><br><span class="line">63</span><br><span class="line">64</span><br><span class="line">65</span><br><span class="line">66</span><br><span class="line">67</span><br><span class="line">68</span><br><span class="line">69</span><br><span class="line">70</span><br><span class="line">71</span><br><span class="line">72</span><br><span class="line">73</span><br><span class="line">74</span><br><span class="line">75</span><br><span class="line">76</span><br><span class="line">77</span><br><span class="line">78</span><br><span class="line">79</span><br><span class="line">80</span><br><span class="line">81</span><br><span class="line">82</span><br><span class="line">83</span><br><span class="line">84</span><br><span class="line">85</span><br><span class="line">86</span><br><span class="line">87</span><br><span class="line">88</span><br><span class="line">89</span><br><span class="line">90</span><br><span class="line">91</span><br><span class="line">92</span><br><span class="line">93</span><br><span class="line">94</span><br><span class="line">95</span><br><span class="line">96</span><br><span class="line">97</span><br><span class="line">98</span><br><span class="line">99</span><br><span class="line">100</span><br><span class="line">101</span><br><span class="line">102</span><br><span class="line">103</span><br><span class="line">104</span><br><span class="line">105</span><br><span class="line">106</span><br><span class="line">107</span><br><span class="line">108</span><br><span class="line">109</span><br><span class="line">110</span><br><span class="line">111</span><br><span class="line">112</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- -------------------------------------------</span></span><br><span class="line"><span class="comment">-- Sams Teach Yourself SQL in 10 Minutes</span></span><br><span class="line"><span class="comment">-- http://forta.com/books/0672336073/</span></span><br><span class="line"><span class="comment">-- Example table population scripts for MySQL.</span></span><br><span class="line"><span class="comment">-- -------------------------------------------</span></span><br><span class="line"></span><br><span class="line"></span><br><span class="line"><span class="comment">-- ------------------------</span></span><br><span class="line"><span class="comment">-- Populate Customers table</span></span><br><span class="line"><span class="comment">-- ------------------------</span></span><br><span class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> Customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)</span><br><span class="line"><span class="keyword">VALUES</span>(<span class="string">'1000000001'</span>, <span class="string">'Village Toys'</span>, <span class="string">'200 Maple Lane'</span>, <span class="string">'Detroit'</span>, <span class="string">'MI'</span>, <span class="string">'44444'</span>, <span class="string">'USA'</span>, <span class="string">'John Smith'</span>, <span class="string">'sales@villagetoys.com'</span>);</span><br><span class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> Customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact)</span><br><span class="line"><span class="keyword">VALUES</span>(<span class="string">'1000000002'</span>, <span class="string">'Kids Place'</span>, <span class="string">'333 South Lake Drive'</span>, <span class="string">'Columbus'</span>, <span class="string">'OH'</span>, <span class="string">'43333'</span>, <span class="string">'USA'</span>, <span class="string">'Michelle Green'</span>);</span><br><span class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> Customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)</span><br><span class="line"><span class="keyword">VALUES</span>(<span class="string">'1000000003'</span>, <span class="string">'Fun4All'</span>, <span class="string">'1 Sunny Place'</span>, <span class="string">'Muncie'</span>, <span class="string">'IN'</span>, <span class="string">'42222'</span>, <span class="string">'USA'</span>, <span class="string">'Jim Jones'</span>, <span class="string">'jjones@fun4all.com'</span>);</span><br><span class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> Customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)</span><br><span class="line"><span class="keyword">VALUES</span>(<span class="string">'1000000004'</span>, <span class="string">'Fun4All'</span>, <span class="string">'829 Riverside Drive'</span>, <span class="string">'Phoenix'</span>, <span class="string">'AZ'</span>, <span class="string">'88888'</span>, <span class="string">'USA'</span>, <span class="string">'Denise L. Stephens'</span>, <span class="string">'dstephens@fun4all.com'</span>);</span><br><span class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> Customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact)</span><br><span class="line"><span class="keyword">VALUES</span>(<span class="string">'1000000005'</span>, <span class="string">'The Toy Store'</span>, <span class="string">'4545 53rd Street'</span>, <span class="string">'Chicago'</span>, <span class="string">'IL'</span>, <span class="string">'54545'</span>, <span class="string">'USA'</span>, <span class="string">'Kim Howard'</span>);</span><br><span class="line"></span><br><span class="line"><span class="comment">-- ----------------------</span></span><br><span class="line"><span class="comment">-- Populate Vendors table</span></span><br><span class="line"><span class="comment">-- ----------------------</span></span><br><span class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> Vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)</span><br><span class="line"><span class="keyword">VALUES</span>(<span class="string">'BRS01'</span>,<span class="string">'Bears R Us'</span>,<span class="string">'123 Main Street'</span>,<span class="string">'Bear Town'</span>,<span class="string">'MI'</span>,<span class="string">'44444'</span>, <span class="string">'USA'</span>);</span><br><span class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> Vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)</span><br><span class="line"><span class="keyword">VALUES</span>(<span class="string">'BRE02'</span>,<span class="string">'Bear Emporium'</span>,<span class="string">'500 Park Street'</span>,<span class="string">'Anytown'</span>,<span class="string">'OH'</span>,<span class="string">'44333'</span>, <span class="string">'USA'</span>);</span><br><span class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> Vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)</span><br><span class="line"><span class="keyword">VALUES</span>(<span class="string">'DLL01'</span>,<span class="string">'Doll House Inc.'</span>,<span class="string">'555 High Street'</span>,<span class="string">'Dollsville'</span>,<span class="string">'CA'</span>,<span class="string">'99999'</span>, <span class="string">'USA'</span>);</span><br><span class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> Vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)</span><br><span class="line"><span class="keyword">VALUES</span>(<span class="string">'FRB01'</span>,<span class="string">'Furball Inc.'</span>,<span class="string">'1000 5th Avenue'</span>,<span class="string">'New York'</span>,<span class="string">'NY'</span>,<span class="string">'11111'</span>, <span class="string">'USA'</span>);</span><br><span class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> Vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)</span><br><span class="line"><span class="keyword">VALUES</span>(<span class="string">'FNG01'</span>,<span class="string">'Fun and Games'</span>,<span class="string">'42 Galaxy Road'</span>,<span class="string">'London'</span>, <span class="literal">NULL</span>,<span class="string">'N16 6PS'</span>, <span class="string">'England'</span>);</span><br><span class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> Vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)</span><br><span class="line"><span class="keyword">VALUES</span>(<span class="string">'JTS01'</span>,<span class="string">'Jouets et ours'</span>,<span class="string">'1 Rue Amusement'</span>,<span class="string">'Paris'</span>, <span class="literal">NULL</span>,<span class="string">'45678'</span>, <span class="string">'France'</span>);</span><br><span class="line"></span><br><span class="line"><span class="comment">-- -----------------------</span></span><br><span class="line"><span class="comment">-- Populate Products table</span></span><br><span class="line"><span class="comment">-- -----------------------</span></span><br><span class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> Products(prod_id, vend_id, prod_name, prod_price, prod_desc)</span><br><span class="line"><span class="keyword">VALUES</span>(<span class="string">'BR01'</span>, <span class="string">'BRS01'</span>, <span class="string">'8 inch teddy bear'</span>, <span class="number">5.99</span>, <span class="string">'8 inch teddy bear, comes with cap and jacket'</span>);</span><br><span class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> Products(prod_id, vend_id, prod_name, prod_price, prod_desc)</span><br><span class="line"><span class="keyword">VALUES</span>(<span class="string">'BR02'</span>, <span class="string">'BRS01'</span>, <span class="string">'12 inch teddy bear'</span>, <span class="number">8.99</span>, <span class="string">'12 inch teddy bear, comes with cap and jacket'</span>);</span><br><span class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> Products(prod_id, vend_id, prod_name, prod_price, prod_desc)</span><br><span class="line"><span class="keyword">VALUES</span>(<span class="string">'BR03'</span>, <span class="string">'BRS01'</span>, <span class="string">'18 inch teddy bear'</span>, <span class="number">11.99</span>, <span class="string">'18 inch teddy bear, comes with cap and jacket'</span>);</span><br><span class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> Products(prod_id, vend_id, prod_name, prod_price, prod_desc)</span><br><span class="line"><span class="keyword">VALUES</span>(<span class="string">'BNBG01'</span>, <span class="string">'DLL01'</span>, <span class="string">'Fish bean bag toy'</span>, <span class="number">3.49</span>, <span class="string">'Fish bean bag toy, complete with bean bag worms with which to feed it'</span>);</span><br><span class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> Products(prod_id, vend_id, prod_name, prod_price, prod_desc)</span><br><span class="line"><span class="keyword">VALUES</span>(<span class="string">'BNBG02'</span>, <span class="string">'DLL01'</span>, <span class="string">'Bird bean bag toy'</span>, <span class="number">3.49</span>, <span class="string">'Bird bean bag toy, eggs are not included'</span>);</span><br><span class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> Products(prod_id, vend_id, prod_name, prod_price, prod_desc)</span><br><span class="line"><span class="keyword">VALUES</span>(<span class="string">'BNBG03'</span>, <span class="string">'DLL01'</span>, <span class="string">'Rabbit bean bag toy'</span>, <span class="number">3.49</span>, <span class="string">'Rabbit bean bag toy, comes with bean bag carrots'</span>);</span><br><span class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> Products(prod_id, vend_id, prod_name, prod_price, prod_desc)</span><br><span class="line"><span class="keyword">VALUES</span>(<span class="string">'RGAN01'</span>, <span class="string">'DLL01'</span>, <span class="string">'Raggedy Ann'</span>, <span class="number">4.99</span>, <span class="string">'18 inch Raggedy Ann doll'</span>);</span><br><span class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> Products(prod_id, vend_id, prod_name, prod_price, prod_desc)</span><br><span class="line"><span class="keyword">VALUES</span>(<span class="string">'RYL01'</span>, <span class="string">'FNG01'</span>, <span class="string">'King doll'</span>, <span class="number">9.49</span>, <span class="string">'12 inch king doll with royal garments and crown'</span>);</span><br><span class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> Products(prod_id, vend_id, prod_name, prod_price, prod_desc)</span><br><span class="line"><span class="keyword">VALUES</span>(<span class="string">'RYL02'</span>, <span class="string">'FNG01'</span>, <span class="string">'Queen doll'</span>, <span class="number">9.49</span>, <span class="string">'12 inch queen doll with royal garments and crown'</span>);</span><br><span class="line"></span><br><span class="line"><span class="comment">-- ---------------------</span></span><br><span class="line"><span class="comment">-- Populate Orders table</span></span><br><span class="line"><span class="comment">-- ---------------------</span></span><br><span class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> Orders(order_num, order_date, cust_id)</span><br><span class="line"><span class="keyword">VALUES</span>(<span class="number">20005</span>, <span class="string">'2012-05-01'</span>, <span class="string">'1000000001'</span>);</span><br><span class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> Orders(order_num, order_date, cust_id)</span><br><span class="line"><span class="keyword">VALUES</span>(<span class="number">20006</span>, <span class="string">'2012-01-12'</span>, <span class="string">'1000000003'</span>);</span><br><span class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> Orders(order_num, order_date, cust_id)</span><br><span class="line"><span class="keyword">VALUES</span>(<span class="number">20007</span>, <span class="string">'2012-01-30'</span>, <span class="string">'1000000004'</span>);</span><br><span class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> Orders(order_num, order_date, cust_id)</span><br><span class="line"><span class="keyword">VALUES</span>(<span class="number">20008</span>, <span class="string">'2012-02-03'</span>, <span class="string">'1000000005'</span>);</span><br><span class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> Orders(order_num, order_date, cust_id)</span><br><span class="line"><span class="keyword">VALUES</span>(<span class="number">20009</span>, <span class="string">'2012-02-08'</span>, <span class="string">'1000000001'</span>);</span><br><span class="line"></span><br><span class="line"><span class="comment">-- -------------------------</span></span><br><span class="line"><span class="comment">-- Populate OrderItems table</span></span><br><span class="line"><span class="comment">-- -------------------------</span></span><br><span class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> OrderItems(order_num, order_item, prod_id, quantity, item_price)</span><br><span class="line"><span class="keyword">VALUES</span>(<span class="number">20005</span>, <span class="number">1</span>, <span class="string">'BR01'</span>, <span class="number">100</span>, <span class="number">5.49</span>);</span><br><span class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> OrderItems(order_num, order_item, prod_id, quantity, item_price)</span><br><span class="line"><span class="keyword">VALUES</span>(<span class="number">20005</span>, <span class="number">2</span>, <span class="string">'BR03'</span>, <span class="number">100</span>, <span class="number">10.99</span>);</span><br><span class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> OrderItems(order_num, order_item, prod_id, quantity, item_price)</span><br><span class="line"><span class="keyword">VALUES</span>(<span class="number">20006</span>, <span class="number">1</span>, <span class="string">'BR01'</span>, <span class="number">20</span>, <span class="number">5.99</span>);</span><br><span class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> OrderItems(order_num, order_item, prod_id, quantity, item_price)</span><br><span class="line"><span class="keyword">VALUES</span>(<span class="number">20006</span>, <span class="number">2</span>, <span class="string">'BR02'</span>, <span class="number">10</span>, <span class="number">8.99</span>);</span><br><span class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> OrderItems(order_num, order_item, prod_id, quantity, item_price)</span><br><span class="line"><span class="keyword">VALUES</span>(<span class="number">20006</span>, <span class="number">3</span>, <span class="string">'BR03'</span>, <span class="number">10</span>, <span class="number">11.99</span>);</span><br><span class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> OrderItems(order_num, order_item, prod_id, quantity, item_price)</span><br><span class="line"><span class="keyword">VALUES</span>(<span class="number">20007</span>, <span class="number">1</span>, <span class="string">'BR03'</span>, <span class="number">50</span>, <span class="number">11.49</span>);</span><br><span class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> OrderItems(order_num, order_item, prod_id, quantity, item_price)</span><br><span class="line"><span class="keyword">VALUES</span>(<span class="number">20007</span>, <span class="number">2</span>, <span class="string">'BNBG01'</span>, <span class="number">100</span>, <span class="number">2.99</span>);</span><br><span class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> OrderItems(order_num, order_item, prod_id, quantity, item_price)</span><br><span class="line"><span class="keyword">VALUES</span>(<span class="number">20007</span>, <span class="number">3</span>, <span class="string">'BNBG02'</span>, <span class="number">100</span>, <span class="number">2.99</span>);</span><br><span class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> OrderItems(order_num, order_item, prod_id, quantity, item_price)</span><br><span class="line"><span class="keyword">VALUES</span>(<span class="number">20007</span>, <span class="number">4</span>, <span class="string">'BNBG03'</span>, <span class="number">100</span>, <span class="number">2.99</span>);</span><br><span class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> OrderItems(order_num, order_item, prod_id, quantity, item_price)</span><br><span class="line"><span class="keyword">VALUES</span>(<span class="number">20007</span>, <span class="number">5</span>, <span class="string">'RGAN01'</span>, <span class="number">50</span>, <span class="number">4.49</span>);</span><br><span class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> OrderItems(order_num, order_item, prod_id, quantity, item_price)</span><br><span class="line"><span class="keyword">VALUES</span>(<span class="number">20008</span>, <span class="number">1</span>, <span class="string">'RGAN01'</span>, <span class="number">5</span>, <span class="number">4.99</span>);</span><br><span class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> OrderItems(order_num, order_item, prod_id, quantity, item_price)</span><br><span class="line"><span class="keyword">VALUES</span>(<span class="number">20008</span>, <span class="number">2</span>, <span class="string">'BR03'</span>, <span class="number">5</span>, <span class="number">11.99</span>);</span><br><span class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> OrderItems(order_num, order_item, prod_id, quantity, item_price)</span><br><span class="line"><span class="keyword">VALUES</span>(<span class="number">20008</span>, <span class="number">3</span>, <span class="string">'BNBG01'</span>, <span class="number">10</span>, <span class="number">3.49</span>);</span><br><span class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> OrderItems(order_num, order_item, prod_id, quantity, item_price)</span><br><span class="line"><span class="keyword">VALUES</span>(<span class="number">20008</span>, <span class="number">4</span>, <span class="string">'BNBG02'</span>, <span class="number">10</span>, <span class="number">3.49</span>);</span><br><span class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> OrderItems(order_num, order_item, prod_id, quantity, item_price)</span><br><span class="line"><span class="keyword">VALUES</span>(<span class="number">20008</span>, <span class="number">5</span>, <span class="string">'BNBG03'</span>, <span class="number">10</span>, <span class="number">3.49</span>);</span><br><span class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> OrderItems(order_num, order_item, prod_id, quantity, item_price)</span><br><span class="line"><span class="keyword">VALUES</span>(<span class="number">20009</span>, <span class="number">1</span>, <span class="string">'BNBG01'</span>, <span class="number">250</span>, <span class="number">2.49</span>);</span><br><span class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> OrderItems(order_num, order_item, prod_id, quantity, item_price)</span><br><span class="line"><span class="keyword">VALUES</span>(<span class="number">20009</span>, <span class="number">2</span>, <span class="string">'BNBG02'</span>, <span class="number">250</span>, <span class="number">2.49</span>);</span><br><span class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> OrderItems(order_num, order_item, prod_id, quantity, item_price)</span><br><span class="line"><span class="keyword">VALUES</span>(<span class="number">20009</span>, <span class="number">3</span>, <span class="string">'BNBG03'</span>, <span class="number">250</span>, <span class="number">2.49</span>);</span><br></pre></td></tr></table></figure>


    </div>

    
    
    
      


      <footer class="post-footer">

        


        
    <div class="post-nav">
      <div class="post-nav-item">
    <a href="/2020/07/02/%E4%B8%AD%E5%9B%BD%E8%BF%91%E4%BB%A3%E5%8F%B2/%E4%B8%AD%E5%9B%BD%E8%BF%91%E4%BB%A3%E5%8F%B2%E7%BA%B2%E8%A6%81/" rel="prev" title="中国近代史纲要">
      <i class="fa fa-chevron-left"></i> 中国近代史纲要
    </a></div>
      <div class="post-nav-item">
    <a href="/2020/07/09/%E7%AE%97%E6%B3%95/" rel="next" title="算法">
      算法 <i class="fa fa-chevron-right"></i>
    </a></div>
    </div>
      </footer>
    
  </article>
  
  
  



          </div>
          

<script>
  window.addEventListener('tabs:register', () => {
    let { activeClass } = CONFIG.comments;
    if (CONFIG.comments.storage) {
      activeClass = localStorage.getItem('comments_active') || activeClass;
    }
    if (activeClass) {
      let activeTab = document.querySelector(`a[href="#comment-${activeClass}"]`);
      if (activeTab) {
        activeTab.click();
      }
    }
  });
  if (CONFIG.comments.storage) {
    window.addEventListener('tabs:click', event => {
      if (!event.target.matches('.tabs-comment .tab-content .tab-pane')) return;
      let commentClass = event.target.classList[1];
      localStorage.setItem('comments_active', commentClass);
    });
  }
</script>

        </div>
          
  
  <div class="toggle sidebar-toggle">
    <span class="toggle-line toggle-line-first"></span>
    <span class="toggle-line toggle-line-middle"></span>
    <span class="toggle-line toggle-line-last"></span>
  </div>

  <aside class="sidebar">
    <div class="sidebar-inner">

      <ul class="sidebar-nav motion-element">
        <li class="sidebar-nav-toc">
          文章目录
        </li>
        <li class="sidebar-nav-overview">
          站点概览
        </li>
      </ul>

      <!--noindex-->
      <div class="post-toc-wrap sidebar-panel">
          <div class="post-toc motion-element"><ol class="nav"><li class="nav-item nav-level-2"><a class="nav-link" href="#数据库简介"><span class="nav-number">1.</span> <span class="nav-text">数据库简介</span></a><ol class="nav-child"><li class="nav-item nav-level-3"><a class="nav-link" href="#数据库（DB，Data-Base）"><span class="nav-number">1.1.</span> <span class="nav-text">数据库（DB，Data Base）</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#数据库管理系统（DBMS，Database-Management-System）"><span class="nav-number">1.2.</span> <span class="nav-text">数据库管理系统（DBMS，Database Management System）</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#MySQL"><span class="nav-number">1.3.</span> <span class="nav-text">MySQL</span></a></li></ol></li><li class="nav-item nav-level-2"><a class="nav-link" href="#MySQL的安装"><span class="nav-number">2.</span> <span class="nav-text">MySQL的安装</span></a><ol class="nav-child"><li class="nav-item nav-level-3"><a class="nav-link" href="#Windows下的安装"><span class="nav-number">2.1.</span> <span class="nav-text">Windows下的安装</span></a></li></ol></li><li class="nav-item nav-level-2"><a class="nav-link" href="#使用Navicat进行可视化操作"><span class="nav-number">3.</span> <span class="nav-text">使用Navicat进行可视化操作</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#SQL语句"><span class="nav-number">4.</span> <span class="nav-text">SQL语句</span></a><ol class="nav-child"><li class="nav-item nav-level-3"><a class="nav-link" href="#语法规范"><span class="nav-number">4.1.</span> <span class="nav-text">语法规范</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#检索数据"><span class="nav-number">4.2.</span> <span class="nav-text">检索数据</span></a><ol class="nav-child"><li class="nav-item nav-level-4"><a class="nav-link" href="#SELECT-语句"><span class="nav-number">4.2.1.</span> <span class="nav-text">SELECT 语句</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#检索单个列"><span class="nav-number">4.2.2.</span> <span class="nav-text">检索单个列</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#检索多个列"><span class="nav-number">4.2.3.</span> <span class="nav-text">检索多个列</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#检索所有列"><span class="nav-number">4.2.4.</span> <span class="nav-text">检索所有列</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#检索不同的值"><span class="nav-number">4.2.5.</span> <span class="nav-text">检索不同的值</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#限制结果"><span class="nav-number">4.2.6.</span> <span class="nav-text">限制结果</span></a></li></ol></li><li class="nav-item nav-level-3"><a class="nav-link" href="#排序检索数据"><span class="nav-number">4.3.</span> <span class="nav-text">排序检索数据</span></a><ol class="nav-child"><li class="nav-item nav-level-4"><a class="nav-link" href="#排序数据"><span class="nav-number">4.3.1.</span> <span class="nav-text">排序数据</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#按多个列排序"><span class="nav-number">4.3.2.</span> <span class="nav-text">按多个列排序</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#按列位置排序"><span class="nav-number">4.3.3.</span> <span class="nav-text">按列位置排序</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#指定排序方向"><span class="nav-number">4.3.4.</span> <span class="nav-text">指定排序方向</span></a></li></ol></li><li class="nav-item nav-level-3"><a class="nav-link" href="#过滤数据"><span class="nav-number">4.4.</span> <span class="nav-text">过滤数据</span></a><ol class="nav-child"><li class="nav-item nav-level-4"><a class="nav-link" href="#使用-WHERE-子句"><span class="nav-number">4.4.1.</span> <span class="nav-text">使用 WHERE 子句</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#WHERE-子句操作符"><span class="nav-number">4.4.2.</span> <span class="nav-text">WHERE 子句操作符</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#检查单个值"><span class="nav-number">4.4.3.</span> <span class="nav-text">检查单个值</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#不匹配检查"><span class="nav-number">4.4.4.</span> <span class="nav-text">不匹配检查</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#范围值检查"><span class="nav-number">4.4.5.</span> <span class="nav-text">范围值检查</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#空值检查"><span class="nav-number">4.4.6.</span> <span class="nav-text">空值检查</span></a></li></ol></li><li class="nav-item nav-level-3"><a class="nav-link" href="#高级数据过滤"><span class="nav-number">4.5.</span> <span class="nav-text">高级数据过滤</span></a><ol class="nav-child"><li class="nav-item nav-level-4"><a class="nav-link" href="#组合-WHERE-子句"><span class="nav-number">4.5.1.</span> <span class="nav-text">组合 WHERE 子句</span></a><ol class="nav-child"><li class="nav-item nav-level-5"><a class="nav-link" href="#AND操作符"><span class="nav-number">4.5.1.1.</span> <span class="nav-text">AND操作符</span></a></li><li class="nav-item nav-level-5"><a class="nav-link" href="#OR操作符"><span class="nav-number">4.5.1.2.</span> <span class="nav-text">OR操作符</span></a></li><li class="nav-item nav-level-5"><a class="nav-link" href="#求值顺序"><span class="nav-number">4.5.1.3.</span> <span class="nav-text">求值顺序</span></a></li></ol></li><li class="nav-item nav-level-4"><a class="nav-link" href="#IN-操作符"><span class="nav-number">4.5.2.</span> <span class="nav-text">IN 操作符</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#NOT-操作符"><span class="nav-number">4.5.3.</span> <span class="nav-text">NOT 操作符</span></a></li></ol></li><li class="nav-item nav-level-3"><a class="nav-link" href="#用通配符进行过滤"><span class="nav-number">4.6.</span> <span class="nav-text">用通配符进行过滤</span></a><ol class="nav-child"><li class="nav-item nav-level-4"><a class="nav-link" href="#LIKE-操作符"><span class="nav-number">4.6.1.</span> <span class="nav-text">LIKE 操作符</span></a><ol class="nav-child"><li class="nav-item nav-level-5"><a class="nav-link" href="#百分号（-）通配符"><span class="nav-number">4.6.1.1.</span> <span class="nav-text">百分号（%）通配符</span></a></li><li class="nav-item nav-level-5"><a class="nav-link" href="#下划线（-）通配符"><span class="nav-number">4.6.1.2.</span> <span class="nav-text">下划线（_）通配符</span></a></li></ol></li><li class="nav-item nav-level-4"><a class="nav-link" href="#使用通配符的技巧"><span class="nav-number">4.6.2.</span> <span class="nav-text">使用通配符的技巧</span></a></li></ol></li><li class="nav-item nav-level-3"><a class="nav-link" href="#用正则表达式进行搜索"><span class="nav-number">4.7.</span> <span class="nav-text">用正则表达式进行搜索</span></a><ol class="nav-child"><li class="nav-item nav-level-4"><a class="nav-link" href="#基本字符匹配"><span class="nav-number">4.7.1.</span> <span class="nav-text">基本字符匹配</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#进行OR匹配"><span class="nav-number">4.7.2.</span> <span class="nav-text">进行OR匹配</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#匹配几个字符之一"><span class="nav-number">4.7.3.</span> <span class="nav-text">匹配几个字符之一</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#匹配范围"><span class="nav-number">4.7.4.</span> <span class="nav-text">匹配范围</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#匹配特殊字符"><span class="nav-number">4.7.5.</span> <span class="nav-text">匹配特殊字符</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#匹配字符类"><span class="nav-number">4.7.6.</span> <span class="nav-text">匹配字符类</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#匹配多个实例"><span class="nav-number">4.7.7.</span> <span class="nav-text">匹配多个实例</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#定位符"><span class="nav-number">4.7.8.</span> <span class="nav-text">定位符</span></a></li></ol></li><li class="nav-item nav-level-3"><a class="nav-link" href="#数据库定义语言（DDL）"><span class="nav-number">4.8.</span> <span class="nav-text">数据库定义语言（DDL）</span></a><ol class="nav-child"><li class="nav-item nav-level-4"><a class="nav-link" href="#操作数据库"><span class="nav-number">4.8.1.</span> <span class="nav-text">操作数据库</span></a><ol class="nav-child"><li class="nav-item nav-level-5"><a class="nav-link" href="#查-Retrieve"><span class="nav-number">4.8.1.1.</span> <span class="nav-text">查(Retrieve)</span></a></li><li class="nav-item nav-level-5"><a class="nav-link" href="#增-Create"><span class="nav-number">4.8.1.2.</span> <span class="nav-text">增(Create)</span></a></li><li class="nav-item nav-level-5"><a class="nav-link" href="#改-Update"><span class="nav-number">4.8.1.3.</span> <span class="nav-text">改(Update)</span></a></li><li class="nav-item nav-level-5"><a class="nav-link" href="#删-Delete"><span class="nav-number">4.8.1.4.</span> <span class="nav-text">删(Delete)</span></a></li></ol></li><li class="nav-item nav-level-4"><a class="nav-link" href="#操作表"><span class="nav-number">4.8.2.</span> <span class="nav-text">操作表</span></a></li></ol></li><li class="nav-item nav-level-3"><a class="nav-link" href="#数据库操作语言（DML）"><span class="nav-number">4.9.</span> <span class="nav-text">数据库操作语言（DML）</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#数据库查询语言（DQL）"><span class="nav-number">4.10.</span> <span class="nav-text">数据库查询语言（DQL）</span></a><ol class="nav-child"><li class="nav-item nav-level-4"><a class="nav-link" href="#基础查询"><span class="nav-number">4.10.1.</span> <span class="nav-text">基础查询</span></a></li></ol></li><li class="nav-item nav-level-3"><a class="nav-link" href="#数据库控制语言（DCL）"><span class="nav-number">4.11.</span> <span class="nav-text">数据库控制语言（DCL）</span></a></li></ol></li><li class="nav-item nav-level-2"><a class="nav-link" href="#示例数据"><span class="nav-number">5.</span> <span class="nav-text">示例数据</span></a></li></ol></div>
      </div>
      <!--/noindex-->

      <div class="site-overview-wrap sidebar-panel">
        <div class="site-author motion-element" itemprop="author" itemscope itemtype="http://schema.org/Person">
    <img class="site-author-image" itemprop="image" alt="Lanqilu"
      src="http://img.whl123456.top/image/avatar.jpg">
  <p class="site-author-name" itemprop="name">Lanqilu</p>
  <div class="site-description" itemprop="description"></div>
</div>
<div class="site-state-wrap motion-element">
  <nav class="site-state">
      <div class="site-state-item site-state-posts">
          <a href="/archives/">
        
          <span class="site-state-item-count">85</span>
          <span class="site-state-item-name">日志</span>
        </a>
      </div>
      <div class="site-state-item site-state-categories">
            <a href="/categories/">
          
        <span class="site-state-item-count">18</span>
        <span class="site-state-item-name">分类</span></a>
      </div>
      <div class="site-state-item site-state-tags">
            <a href="/tags/">
          
        <span class="site-state-item-count">39</span>
        <span class="site-state-item-name">标签</span></a>
      </div>
  </nav>
</div>
  <div class="links-of-author motion-element">
      <span class="links-of-author-item">
        <a href="https://github.com/lanqilu" title="GitHub → https:&#x2F;&#x2F;github.com&#x2F;lanqilu" rel="noopener" target="_blank"><i class="fa fa-fw fa-github"></i></a>
      </span>
      <span class="links-of-author-item">
        <a href="http://mail.qq.com/cgi-bin/qm_share?t=qm_mailme&email=zqKvoL_noruOqKG2o6_nouCtoaM" title="E-Mail → http:&#x2F;&#x2F;mail.qq.com&#x2F;cgi-bin&#x2F;qm_share?t&#x3D;qm_mailme&amp;email&#x3D;zqKvoL_noruOqKG2o6_nouCtoaM" rel="noopener" target="_blank"><i class="fa fa-fw fa-envelope"></i></a>
      </span>
  </div>



      </div>
        <div class="back-to-top motion-element">
          <i class="fa fa-arrow-up"></i>
          <span>0%</span>
        </div>

    </div>
  </aside>
  <div id="sidebar-dimmer"></div>


      </div>
    </main>

    <footer class="footer">
      <div class="footer-inner">
        

        

<div class="copyright">
  
  &copy; 2019 – 
  <span itemprop="copyrightYear">2020</span>
  <span class="with-love">
    <i class="fa fa-heart"></i>
  </span>
  <span class="author" itemprop="copyrightHolder">Lanqilu</span>
</div>
  <div class="powered-by">由 <a href="https://hexo.io/" class="theme-link" rel="noopener" target="_blank">Hexo</a> & <a href="https://theme-next.org/" class="theme-link" rel="noopener" target="_blank">NexT.Gemini</a> 强力驱动
  </div>

        








      </div>
    </footer>
  </div>

  
  <script src="/lib/anime.min.js"></script>
  <script src="/lib/pjax/pjax.min.js"></script>
  <script src="//cdn.jsdelivr.net/npm/jquery@3/dist/jquery.min.js"></script>
  <script src="//cdn.jsdelivr.net/gh/fancyapps/fancybox@3/dist/jquery.fancybox.min.js"></script>
  <script src="//cdn.jsdelivr.net/npm/pangu@4/dist/browser/pangu.min.js"></script>
  <script src="/lib/velocity/velocity.min.js"></script>
  <script src="/lib/velocity/velocity.ui.min.js"></script>

<script src="/js/utils.js"></script>

<script src="/js/motion.js"></script>


<script src="/js/schemes/pisces.js"></script>


<script src="/js/next-boot.js"></script>

  <script>
var pjax = new Pjax({
  selectors: [
    'head title',
    '#page-configurations',
    '.content-wrap',
    '.post-toc-wrap',
    '.languages',
    '#pjax'
  ],
  switches: {
    '.post-toc-wrap': Pjax.switches.innerHTML
  },
  analytics: false,
  cacheBust: false,
  scrollTo : !CONFIG.bookmark.enable
});

window.addEventListener('pjax:success', () => {
  document.querySelectorAll('script[data-pjax], script#page-configurations, #pjax script').forEach(element => {
    var code = element.text || element.textContent || element.innerHTML || '';
    var parent = element.parentNode;
    parent.removeChild(element);
    var script = document.createElement('script');
    if (element.id) {
      script.id = element.id;
    }
    if (element.className) {
      script.className = element.className;
    }
    if (element.type) {
      script.type = element.type;
    }
    if (element.src) {
      script.src = element.src;
      // Force synchronous loading of peripheral JS.
      script.async = false;
    }
    if (element.dataset.pjax !== undefined) {
      script.dataset.pjax = '';
    }
    if (code !== '') {
      script.appendChild(document.createTextNode(code));
    }
    parent.appendChild(script);
  });
  NexT.boot.refresh();
  // Define Motion Sequence & Bootstrap Motion.
  if (CONFIG.motion.enable) {
    NexT.motion.integrator
      .init()
      .add(NexT.motion.middleWares.subMenu)
      .add(NexT.motion.middleWares.postList)
      .bootstrap();
  }
  NexT.utils.updateSidebarPosition();
});
</script>




  




  
<script src="/js/local-search.js"></script>













    <div id="pjax">
  

  

  

    </div>
</body>
</html>
